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