CODEWITHSUNDEEP
datetimetimestampbigintpresto
Nick Knauer
Nick Knauer

Reputation: 4243

Converting bigint to timestamp in presto

I have a column in my dataset that has a datatype of bigint: 

Col1     Col2
   1     1519778444938790
   2     1520563808877450
   3     1519880608427160
   4     1520319586578960
   5     1519999133096120

How do I convert Col2 to the following format:

year-month-day hr:mm:ss

I am not sure what format my current column is in but I know that it is supposed to be a timestamp.

Any help will be great, thanks!

Upvotes: 15

Views: 38398

Answers (4)

Guy
Guy

Reputation: 11

Accepted answer is a bit misleading. You should divide by 1000.0 otherwise you'll lose ms precision and be limited to second precision:

date_format(from_unixtime(col2/1000.0), '%Y-%m-%d %h:%i:%s')

Upvotes: 1

Jerin Mathew
Jerin Mathew

Reputation: 760

If you need to filter the data where the column is in BIGINT Unix format, then you can use the following snippet to compare : from_unixtime(d.started_on /1000) >= CAST('2022-05-10 22:00:00' AS TIMESTAMP )

Upvotes: 1

N Pusapati
N Pusapati

Reputation: 11

I believe the denominator should be 1000000 not 1000. Probably a typo. Anyways juts adding the test results here for others reference.

-- Microseconds

select date_format(from_unixtime(cast('1519778444938790' as bigint)/1000000), '%Y-%m-%d %h:%i:%s');

2018-02-28 12:40:44

Upvotes: 1

kokosing
kokosing

Reputation: 5601

Have you tried to use functions like from_unixtime? You could use it to convert unix time to timestamp, then you could use date_format to display it in way you want. Notice that in your example your unix time is with microseconds, so you might want to convert it first to milliseconds.

I have not tested that but I am assuming that your code should look like:

date_format(from_unixtime(col2/1000), '%Y-%m-%d %h:%i:%s')

Notice that from_unixtime accepts also a time zone.

Please visit this page to see the more details about date related functions: https://docs.starburstdata.com/latest/functions/datetime.html

Upvotes: 25

Related Questions