I have timestamps stored as millis from the epoch, i.e. 1586649607432 represents 2020-04-12 00:00:07.432 UTC.
But it looks like TO_TIMESTAMP only supports Epoch Seconds, not Epoch Milliseconds.
If I do this query:
select TO_TIMESTAMP(1586649607400) as CREATED_AT
I get an invalid date: 52248-12-04 02:03:20.000
But if I divide by 1000:
select TO_TIMESTAMP(1586649607400/1000) as CREATED_AT
I get the valid date and time, but it’s missing the milliseconds, which are critical to my application.
Is there a way in Dremio to convert Epoch Milliseconds to Timestamp?
One way to convert is to do to_timestamp(value/1000) as column_name. See attached screenshot
Sorry ! Missed the part that you already tried “/1000”. Yes milliseconds will get truncated. Will get back to you and see if there is a workaround or we can fix that behavior
This is a known issue with us.
The millisecond needs a code fix. We will keep you posted about the upcoming releases with this fix.
Indeed, the TO_TIME function still only considerates seconds as its calculation basis.
The query below illustrates this behavior.
(/* generates example time */
SELECT CAST(now() AS TIME) AS test_time
, q2 AS
(/* split hour minute second */
,EXTRACT(HOUR FROM q1.test_time) AS hh
,EXTRACT(MINUTE FROM q1.test_time) AS mi
,EXTRACT(SECOND FROM q1.test_time) AS ss
, q3 AS
(/* compute running seconds in a day */
,(q2.hh*3600)+(q2.mi*60)+(q2.ss) AS compound_seconds
,TO_TIME(q3.compound_seconds) AS original_time /* get back to original given time without dealing w/ milisseconds */
The problem however is that by ‘fixing’ this (i.e., changing the calculation basis to milisseconds) you will break any code that is already using it the way it is, generating impact and rework.