PostgreSQL and MySQL Unix Time Conversions

TIMESTAMP / DATETIME to Unix Time

Unix timestamp handling is a bit different in MySQL and PostgreSQL, so when you’re rewriting SQL queries to work with the other database it’s good to know how to convert Unix timestamps.

PostgreSQL
postgres=# SELECT EXTRACT(epoch from timestamp with time zone '2016-02-25 12:15:17+01');
 date_part
------------
 1456398917
(1 row)
MySQL
mysql> SELECT UNIX_TIMESTAMP('2016-02-25 12:15:17+01');
+------------------------------------------+
| UNIX_TIMESTAMP('2016-02-25 12:15:17+01') |
+------------------------------------------+
|                               1456398917 |
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Unix Time to TIMESTAMP / DATETIME

PostgreSQL
postgres=# SELECT TO_TIMESTAMP(1456398917);
      to_timestamp
------------------------
 2016-02-25 12:15:17+01
(1 row)
MySQL
mysql> SELECT FROM_UNIXTIME(1456398917);
+---------------------------+
| FROM_UNIXTIME(1456398917) |
+---------------------------+
| 2016-02-25 12:15:17       |
+---------------------------+
1 row in set (0.01 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.