phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nick Hryhoriev (JIRA)" <>
Subject [jira] [Commented] (PHOENIX-1952) Function to_date(to_char(columns, pattern), pattern) do not work
Date Wed, 10 Jun 2015 07:29:00 GMT


Nick Hryhoriev commented on PHOENIX-1952:

[~jamestaylor]Do i need move issue to resolve, because it don't reproduce on 4.3.1 or it must
be fixed for 4.3.0 too?

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

View raw message