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 a bigint to timestamp.