Serhiy Bilousov created PHOENIX-1744:
----------------------------------------
Summary: 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
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)
