# Epoch Milliseconds to Timestamp

**URL:** <https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541>\
**Category:** Uncategorized\
**Created:** [May 11, 2020, 6:18pm UTC](https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541 "2020-05-11T18:18:17Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pzybrick](https://avatars.discourse-cdn.com/v4/letter/p/ec9cab/32.png) [@pzybrick](https://community.dremio.com/u/pzybrick)\
**Post date:** [May 11, 2020, 6:18pm UTC](https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541/1 "2020-05-11T18:18:17Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [May 11, 2020, 6:21pm UTC](https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541/2 "2020-05-11T18:21:20Z")

</div>

Hi @pzybrick

One way to convert is to do to\_timestamp(value/1000) as column\_name. See attached screenshot

 ![To-Timestamp](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/3/352391454a47f00cf864d103348855a35ef59fac.png)

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [May 11, 2020, 6:23pm UTC](https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541/3 "2020-05-11T18:23:35Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![Rakesh\_Malugu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/rakesh_malugu/32/2384_2.png) [@Rakesh\_Malugu](https://community.dremio.com/u/Rakesh_Malugu)\
**Post date:** [May 12, 2020, 5:52am UTC](https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541/4 "2020-05-12T05:52:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![anitelleth](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/anitelleth/32/3057_2.png) [@anitelleth](https://community.dremio.com/u/anitelleth)\
**Post date:** [September 21, 2020, 10:43pm UTC](https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541/5 "2020-09-21T22:43:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![tshiran](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/tshiran/32/15_2.png) [@tshiran](https://community.dremio.com/u/tshiran)\
**Post date:** [October 14, 2022, 7:10pm UTC](https://community.dremio.com/t/epoch-milliseconds-to-timestamp/5541/6 "2022-10-14T19:10:34Z")

</div>

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

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

```