Nick Hryhoriev created PHOENIX-1952:
---------------------------------------
Summary: 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
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
