« 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
Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.Trackback Pings
TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/946
Comments
Heh,
I guess each time daylight savings come into affect thousands of bugs are being exposed. I had numerous bugs of this sort with applications I've been working with. Everything seems to know about daylight savings... but still forgets about it in some case.
Couple of years ago I spoke to someone in Google - they mentioned me they to had bugs of this sort and it was $500.000 of lost revenues before it was fixed.
Hope your bug was not that expensive :)
Posted by: Peter Zaitsev at November 3, 2006 5:49 AM