[
https://issues.apache.org/jira/browse/PHOENIX-1744?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14369546#comment-14369546
]
Serhiy Bilousov commented on PHOENIX-1744:
------------------------------------------
q. [~jamestaylor] Phoenix only supports a millisecond Epoch time value. Maybe
what you're asking for is a second Epoch time value.
that exactly what I am asking - seconds epoch time (4 byte integer).
q. [~jamestaylor] That's a fair request, but a different JIRA (and more
involved
Do you want me to file another JIRA for seconds epoch time?
q.[~jamestaylor] how would Phoenix know in your above example if the epoch
represents seconds or milliseconds?).
I would think if it INTEGER than it seconds epoch, If LONG than it
milliseconds. Now for example PHOENIX knows that it INTEGER and not allows to
convert it to TIMESTAMP.
{noformat}
-- 19 Mar 2015 14:46:30 GMT (1426776390 in sec, 1426776390000 in milisec)
select cast(1426776390 AS TIMESTAMP), cast(1426776390000 AS TIMESTAMP) from
system.catalog LIMIT 1;
Error: ERROR 203 (22005): Type mismatch. INTEGER and TIMESTAMP for 1426776390
(state=22005,code=203)
{noformat}
q. [~jamestaylor] I haven't seen millisecond Epoch values stored in an integer
before, so it doesn't seem like something that adds value to support.
You cant. You store seconds Epoch values in integer.
[OpenTSDB - writing data -
timestamps|http://opentsdb.net/docs/build/html/user_guide/writing.html#timestamps]
{quote}
Timestamps
Data can be written to OpenTSDB with second or millisecond resolution.
Timestamps must be integers and be no longer than 13 digits (See first NOTE
below). Millisecond timestamps must be of the format 1364410924250 where the
final three digits represent the milliseconds. Applications that generate
timestamps with more than 13 digits (i.e., greater than millisecond resolution)
must be rounded to a maximum of 13 digits before submitting or an error will be
generated.
Timestamps with second resolution are stored on 2 bytes while millisecond
resolution are stored on 4. Thus if you do not need millisecond resolution or
all of your data points are on 1 second boundaries, we recommend that you
submit timestamps with 10 digits for second resolution so that you can save on
storage space. It's also a good idea to avoid mixing second and millisecond
timestamps for a given time series. Doing so will slow down queries as
iteration across mixed timestamps takes longer than if you only record one type
or the other. OpenTSDB will store whatever you give it.
{quote}
> 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)
