[
https://issues.apache.org/jira/browse/PHOENIX-1744?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14368544#comment-14368544
]
James Taylor commented on PHOENIX-1744:
---------------------------------------
I agree it's a bug for unsigned_long, I just don't see a reason to extend it
beyond that. The value needs to be an Epoch time value, so if you're storing an
INTEGER representing a date/time without milliseconds, you'd want to cast it to
a BIGINT and multiply it by 1000.
> CAST from UNSIGNED_LONG (_INT) to * TIMESTAMP is not supported.
> ---------------------------------------------------------------
>
> Key: PHOENIX-1744
> URL: https://issues.apache.org/jira/browse/PHOENIX-1744
> Project: Phoenix
> Issue Type: Bug
> Reporter: Serhiy Bilousov
> Assignee: Dave Hacker
> Priority: Minor
>
> Epoch time can be represented as INTEGER (up to the seconds) or LONG (up to
> the millisecond). Currently CAST from UNSIGNED_LONG to TIMESTAMP not
> supported by Phoenix.
> It make sense to have support for conversion from epoch (4 bytes or 8 bytes)
> to any datetime like format curently supported by Phoenix (TIME, DATE,
> TIMESTAMP, UNSIGNED_TIME, UNSIGNED_DATE, UNSIGNED_TIMESTAMP).
> HBase shell:
> {noformat}
> create 't','f1'
> put
> 't',"\x00\x00\x00\x01\x00\x00\x00\x01\x00\x00\x01L\x0Fz,\x1E",'f1:c1','test'
> {noformat}
> sqlline:
> {noformat}
> CREATE VIEW vT
> ( a UNSIGNED_INT NOT NULL
> ,b UNSIGNED_INT NOT NULL
> ,ts UNSIGNED_LONG NOT NULL
> CONSTRAINT pk PRIMARY KEY (a, b, ts))
> AS SELECT * FROM "t"
> DEFAULT_COLUMN_FAMILY ='f1';
> select a, b, ts, CAST(1426188807198 AS TIMESTAMP) from vt;
> +----+----+----------------+------------------------------+
> | A | B | TS | TO_TIMESTAMP(1426188807198) |
> +----+----+----------------+------------------------------+
> | 1 | 1 | 1426188807198 | 2015-03-12 19:33:27.198 |
> +----+----+----------------+------------------------------+
> {noformat}
> but
> {noformat}
> select a, b, ts, CAST(ts AS TIMESTAMP) from vt;
> Error: ERROR 203 (22005): Type mismatch. UNSIGNED_LONG and TIMESTAMP for TS
> (state=22005,code=203)
> {noformat}
> As per Gabriel Reid
> {quote}
> As a workaround, you can cast the UNSIGNED_LONG to a BIGINT first, and then
> cast it to a TIMESTAMP, i.e.
> select a, b, ts, CAST(CAST(ts AS BIGINT) AS TIMESTAMP) from vt;
> {quote}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
