[ https://issues.apache.org/jira/browse/PHOENIX-1744?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14375169#comment-14375169 ]
James Taylor commented on PHOENIX-1744: --------------------------------------- +1. [~samarthjain] - please check in to 4.3, 4.x-HBase-0.98, 4.x-HBase-1.x, and master branches on behalf of Dave. > 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 > Labels: 4.3.1 > > 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)