Unix timestamp to readable date timestamp with milliseconds

Dremio University
1

Hi all,

I have source applications publishing all dates in the unix epoch millisecond time format and on reading the data, the field shows up as text data type. I tried convert_to_number and then to timestamp but get erroneous result:

image
image

image
image733×337 29 KB

2

A bit of wrangling led to loss of millis:

image
image808×290 3.91 KB

3

@naveenm You can do to_timestamp(column_name/1000) but will make milliseconds all zeroes, like you said. Will check on the milliseconds part and get back to you

4

@balaji.ramaswamy, it’s absolutely critical to fetch millisecond information for our application as daily cut off times are filtered based on the exact transact times. As you can see from my second reply snapshot above, truncating the millis is achievable but will impact filtering of data. I suppose this should be an easy enhancement on Dremio side?

5

I’m seeing different things though? I’m on 19.0.0

select to_timestamp((cast ('1610431933358' as decimal))/1000)

image
image2712×944 133 KB

2 Likes
6

Thank you @lenoyjacob, this works!