phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nick Hryhoriev (JIRA)" <>
Subject [jira] [Created] (PHOENIX-1952) Function to_date(to_char(columns, pattern), pattern) do not work
Date Wed, 06 May 2015 15:33:05 GMT
Nick Hryhoriev created PHOENIX-1952:

             Summary: Function to_date(to_char(columns, pattern), pattern) do not work
                 Key: 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:
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?


This message was sent by Atlassian JIRA

View raw message