Convert String epoch code from unix timestamp to date using sql

Question

I need your help in converting this event_time to an ASCII SQL formated datatype. A little background: This data was retrieved from a Hadoop Hive data source using Tableau. Also, I was told it's a unix timestamp. I have some extensive research done but seem to not be able to find any solution.

SELECT converted(event_time) from tablename; 

event_time
1610629690320
1610629939669
1610630516715
1610633038207
1610633886798
1610633991600
1610635895132
1610636069966
1610636135413

Is that sample table data or the expected result? We need both anyway. — jarlh, Feb 05 '21 at 14:30
PostGres? duplicate of https://stackoverflow.com/questions/16609722/postgresql-how-to-convert-from-unix-epoch-to-date — Caius Jard, Feb 05 '21 at 14:31
This site can help validate unix dates: https://www.epochconverter.com/ — Gary_W, Feb 05 '21 at 15:32
`select to_timestamp(1610629690320/1000); 2021-01-14 05:08:10-08` The time is in milliseconds so you need to divide by thousands to get to Unix epoch seconds. Then you can use `to_timestamp()`. — Adrian Klaver, Feb 05 '21 at 15:55

score 0 · Answer 1 · answered Feb 05 '21 at 17:22

How to convert unix-timestamp to datetime in Tableau

Create a calculated field:
DATEADD('second', INT([event_time]/1000),#1970-01-01#)

Reference:
https://kb.tableau.com/articles/issue/dates-display-unix-epoch-instead-of-date-datetime?_ga=2.128232396.1788694263.1612488670-1032121342.1608670166

Convert String epoch code from unix timestamp to date using sql

1 Answers1