[
https://issues.apache.org/jira/browse/PHOENIX-1744?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14368575#comment-14368575
]
James Taylor commented on PHOENIX-1744:
---------------------------------------
Phoenix only supports a millisecond Epoch time value. Maybe what you're asking
for is a second Epoch time value. That's a fair request, but a different JIRA
(and more involved - how would Phoenix know in your above example if the epoch
represents seconds or millseconds?). The only thing that's going to work in
Phoenix today is if your numeric value stores the millisecond Epoch value. Any
other number is not going to convert correctly. I haven't seen millisecond
Epoch values stored in an integer before, so it doesn't seem like something
that adds value to support.
> 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)
