« Making Changes to MySQL Source Code | Main | Gapless Playback on iPod is Good »

November 2, 2006

MySQL, Unix Timestamp, and the Lost Hour

Today I fixed a bug in our application that was interesting to figure out. Without a ton of detail, the gist is that if you convert a datetime to unix timestamp in MySQL:

mysql> select UNIX_TIMESTAMP('2006-10-28 04:00:00'); 
+---------------------------------------+
| UNIX_TIMESTAMP('2006-10-28 04:00:00') |
+---------------------------------------+
|                            1162022400 |
+---------------------------------------+
1 row in set (0.00 sec)

And then add 24 hours to it:

mysql> select 1162022400 + (60*60*24);
+-------------------------+
| 1162022400 + (60*60*24) |
+-------------------------+
|              1162108800 |
+-------------------------+
1 row in set (0.00 sec)

And then convert it back to a datetime it magically loses an hour:

mysql> select FROM_UNIXTIME('1162108800');
+-----------------------------+
| FROM_UNIXTIME('1162108800') |
+-----------------------------+
| 2006-10-29 03:00:00         |
+-----------------------------+
1 row in set (0.00 sec)

If you look closely that's just one hour shy of 24 hours.

Here's what's happening. When coverting the unix timestamp back to a datetime the timezone is taken into consideration and it just so happens that between the 28th and the 29th of October 2006 we went off daylight savings time and lost an hour. Now there's a bug that requires some specific information to duplicate. Luckily the support folks had narrowed the issue down to the exact criteria.

Even though I still need the unix timestamp for doing some other date comparisons, the better way to add a day to a date is to use ADDDATE:

mysql> select ADDDATE('2006-10-28 04:00:00', INTERVAL 1 DAY);
+------------------------------------------------+
| ADDDATE('2006-10-28 04:00:00', INTERVAL 1 DAY) |
+------------------------------------------------+
| 2006-10-29 04:00:00                            |
+------------------------------------------------+
1 row in set (0.00 sec)

Posted by mike at November 2, 2006 6:03 PM