Epoch Milliseconds to Timestamp

1

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?

2

Hi @pzybrick

One way to convert is to do to_timestamp(value/1000) as column_name. See attached screenshot

To-Timestamp
To-Timestamp1134×444 27.1 KB

3

@pzybrick

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

Thanks
Bali

4

Hello @pzybrick

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.

Thanks,
Rakesh

5

Indeed, the TO_TIME function still only considerates seconds as its calculation basis.

The query below illustrates this behavior.

WITH 
	q1 AS 
	(/* generates example time */
	SELECT CAST(now() AS TIME) AS test_time 
	)
,	q2 AS 
	(/* split hour minute second */ 
	SELECT 
		 q1.test_time 
		,EXTRACT(HOUR 	FROM q1.test_time)  	AS hh 
		,EXTRACT(MINUTE FROM q1.test_time)	AS mi 
		,EXTRACT(SECOND FROM q1.test_time)  	AS ss 
	FROM
		q1 
	)	
,	q3 AS 
	(/* compute running seconds in a day */ 
	SELECT 
		 q2.* 
		,(q2.hh*3600)+(q2.mi*60)+(q2.ss) AS compound_seconds 
	FROM 
		q2  
	)
SELECT 
	 q3.* 
	,TO_TIME(q3.compound_seconds) AS original_time /* get back to original given time without dealing w/ milisseconds */ 
FROM 
	q3;

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.

6

If the epoch is a string (varchar), you can remove the last three digits first and then cast to integer:

TO_TIMESTAMP(CAST(SUBSTRING(t1."time", 0, LENGTH(t1."time") - 3) AS INT))