How To Convert Bigint to Timestamp with Presto
UNIX timestamps are normally stored as doubles. If you have UNIX timestamps stored as big integers then you may encounter errors when trying to cast them as timestamps:
presto> select col1 from table_a;
col1
------------
1606485526
1606485575
presto> select cast(col1 as timestamp) from table_a;
Query 20201127_150824_00052_xnxra failed: line 1:8: Cannot cast bigint to timestamp
There is a solution! Presto’s from_unixtime()
function takes a UNIX timestamp and returns a timestamp:
presto> select col1,from_unixtime(col1) as ts from table_a;
col1 | ts
------------+-------------------------
1606485526 | 2020-11-27 13:58:46.000
1606485575 | 2020-11-27 13:59:35.000
And we can optionally modify the format of the result by using date_format()
:
presto> select date_format(from_unixtime(col1),'%Y-%m-%d %h:%i%p') from table_a;
_col0
---------------------
2020-11-27 01:58PM
2020-11-27 01:59PM
That’s how to use from_unixtime()
to convert bigint to timestamp.
Want more Presto tips & tricks? Sign up for our Presto community newsletter.