Wednesday, April 01, 2009

UNIX_TIMESTAMP() AND FROM_UNIXTIME()

If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert
between TIMESTAMP values and Unix timestamp values, the conversion
is lossy because the mapping is not one-to-one in both directions.
Because of daylight saving time, it is possible for two UNIX_TIMESTAMP()
to map two TIMESTAMP values to the same Unix timestamp value.
FROM_UNIXTIME() will map that value back to only one of the original
TIMESTAMP values. Here is an example, using TIMESTAMP values in
the CET time zone:

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+

mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00 |
+---------------------------+