[ https://issues.apache.org/jira/browse/PHOENIX-1952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14531199#comment-14531199 ]
James Taylor commented on PHOENIX-1952: --------------------------------------- Did you try this on 4.3.1? Also, you can now cast a BIGINT directly to a DATE, like this: {code} CREATE TABLE my_table(my_big_int BIGINT PRIMARY KEY); SELECT CAST(my_big_int AS DATE) FROM my_table; {code} > Function to_date(to_char(columns, pattern), pattern) do not work > ---------------------------------------------------------------- > > Key: PHOENIX-1952 > URL: https://issues.apache.org/jira/browse/PHOENIX-1952 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.3.0 > Reporter: Nick Hryhoriev > Priority: Critical > Fix For: 4.3.0 > > > In version 4.3.0 > i try to execute such query > TO_DATE(TO_CHAR(ds."DATEMILLIS"/1000,'#'),'ssssssssss') and it's give me > exception to_date('1379390400)' did not match expected date format of ''. > But if i will try such query TO_DATE(TO_CHAR(1379390400,'#'),'ssssssssss') > It's work ok, my phoenix version 4.3.0 > Can you help me, please > Best regards, Nick > P>S : > James Taylor write: > Mina, > You might try something like this: > select TO_CHAR(TO_DATE(TO_CHAR(sendtime,'#'),'S'),'yyyy-MM-dd HH:mm:ss') from > test2; > or another alternative: > select TO_CHAR(TO_DATE('' || sendtime,'S'),'yyyy-MM-dd HH:mm:ss') from test2; > You need to convert the sendtime from a BIGINT to VARCHAR and then to a DATE. > The innermost TO_CHAR converts the BIGINT to a VARCHAR without using any > comma separators. For TO_DATE, the second argument is a format string, with > 'S' being milliseconds. Underneath, we just use new > SimpleDateFormat(pattern).parseObject() to get a DATE - I'm not sure if this > works if you give it just a millisecond value, but it's worth a try. Once you > have a DATE, you can display it in the format you'd like with TO_CHAR. > If this doesn't work, then you could modify the CAST built-in operator to > allow a BIGINT -> DATE/TIME/TIMESTAMP conversion. That would not be hard, as > we use the same serialization format for a BIGINT and a DATE. That would make > a good first contribution. > Another option would be to declare SENDTIME as a DATE or TIME column in your > schema. You can do date arithmetic on these columns as well where the unit is > a DAY as with other RDBMS. Any reason why you didn't go this route initially? > Thanks, > James > DDL -> > Date DATETIME or TIMESTAMP, > State VARCHAR(20), > `County Code` VARCHAR(15), > `Income Bracket` VARCHAR(17), > sale_date BIGINT, > ship_date DATETIME or TIMESTAMP, > Sales DECIMAL, NUMERIC, or FLOAT, > Zipcode VARCHAR(15), > `Product Category` VARCHAR(15), > City VARCHAR(25), > `Product Group` VARCHAR(11), > Satisfaction INTEGER, > SKU VARCHAR(8), > `Planned Sales` DECIMAL, NUMERIC, or FLOAT, > Gender VARCHAR(6), > County VARCHAR(31), > `Review Text` VARCHAR(16000) or TEXT -- This message was sent by Atlassian JIRA (v6.3.4#6332)