sql-info.de
February 17, 2013

MySQL | Convert timestamp to UNIX epoch

Use the function UNIX_TIMESTAMP():

mysql> SELECT ts, UNIX_TIMESTAMP(ts) FROM ts_test;
+---------------------+--------------------+
| ts                  | UNIX_TIMESTAMP(ts) |
+---------------------+--------------------+
| 2013-02-17 16:34:09 |         1361086449 |
+---------------------+--------------------+
1 row in set (0.00 sec)

To get the current epoch, use UNIX_TIMESTAMP() with out any arguments:

mysql> SELECT UNIX_TIMESTAMP();
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1361088422 |
+-----------------------+
1 row in set (0.00 sec)

A slight MySQL oddity: SELECT UNIX_TIMESTAMP(NOW()) is equivalent to SELECT UNIX_TIMESTAMP(), however SELECT UNIX_TIMESTAMP(UTC_TIMESTAMP()) will produce an epoch number in the past.

mysql> SELECT UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(UTC_TIMESTAMP());
+-----------------------+---------------------------------+
| UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP(UTC_TIMESTAMP()) |
+-----------------------+---------------------------------+
|            1361088547 |                      1361056147 |
+-----------------------+---------------------------------+
1 row in set (0.00 sec)
Posted at 8:05 AM