Presto convert timestamp to epoch

Question

In redshift, I can do something like this

date_part(epoch, '2019-03-07 10:17:03.000000') and it would return 1551953823. How do I do this in Presto?

Answer 1

You can either use to_unixtime:

presto> select to_unixtime(timestamp '2019-03-07 10:17:03');
     _col0
---------------
 1.551953823E9
(1 row)

or use date_diff to subtract the timestamp from the epoch timestamp:

presto> select date_diff('second', timestamp '1970-01-01', timestamp '2019-03-07 10:17:03');
   _col0
------------
 1551953823
(1 row)

Note that to_unixtime returns a double representing the number of seconds plus milliseconds as a fractional part. If you want just the seconds, you can cast the result to BIGINT:

presto> select cast(to_unixtime(timestamp '2019-03-07 10:17:03') as bigint);
   _col0
------------
 1551953823
(1 row)