Unix timestamp to readable date timestamp with milliseconds

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:


A bit of wrangling led to loss of millis:

@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

@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?

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

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


Thank you @lenoyjacob, this works!