[
https://issues.apache.org/jira/browse/PHOENIX-1744?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14368515#comment-14368515
]
Serhiy Bilousov commented on PHOENIX-1744:
------------------------------------------
Well, actually we did define column as INTEGER (4 bytes) in our hbase table to
store timestamps when we do not need milliseconds. If my memory serves me well
openTSDB also uses 4 bytes for timestamps.
If you guys think it not worse it nothing I can do about that (until I will be
able to submit pull request) but workaround is just that - workaround. For me
personally having epoch time in the table and instead of doing
CAST(epochmilisecons AS TIMESTAMP) doing CAST(CAST(epochmilisecons AS BIGINT)
AS TIMESTAMP) just does not look very good. + performance hit on unnecessary
CAST.
SB
> 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)
