[PHOENIX-1744] CAST from UNSIGNED_LONG (_INT) to * TIMESTAMP is not supported.

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 4.3.1, 4.4.0
• Labels: 4.3.1
• Assignee: Dave Hacker
• Reporter: Serhiy Bilousov
• Created: 18/Mar/15 13:44
• Updated: 01/Aug/23 12:05
• Resolved: 24/Mar/15 21:03

Description

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:

create 't','f1'
put 't',"\x00\x00\x00\x01\x00\x00\x00\x01\x00\x00\x01L\x0Fz,\x1E",'f1:c1','test'

sqlline:

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      |
+----+----+----------------+------------------------------+

but

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)

As per Gabriel Reid

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;

Issue Links

• links to: GitHub Pull Request #52