[jira] [Resolved] (PHOENIX-1952) Function to_date(to_char(columns, pattern), pattern) do not work

["James Taylor (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-1952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

James Taylor resolved PHOENIX-1952.
-----------------------------------
    Resolution: Duplicate

Duplicate of PHOENIX-2593

> 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
>
> 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)