MySQL is 24 seconds too slow July 20th, 2009

 

1246402800. A perfectly valid UNIX timestamp, but one that created some confusion and head scratching in our office for one of our latest internal projects. The problem came around when converting the timestamp into a MySQL date using the FROM_UNIXTIME function which produced the wrong output.

The timestamp is perfectly valid, it’s the timestamp for 1st June 2009, 00:00:00 (check it, if you don’t believe me). However, when running it through the FROM_UNIXTIME function it outputs:

SELECT FROM_UNIXTIME(1246402800)
> 2009-06-30 23:59:36

It’s 24 seconds out. How on God’s green Earth is that possible? I tried it on another of our servers and it outputs the correct date, leading me us to assume it’s not a problem with MySQL but with our database installation. The MySQL manual states that:

Note: 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.

Our server is set to GMT, but that is besides the point—it’s 24 seconds out, a different timezone would be hours (or half hours) out. As luck would have it, however, we are moving servers this week—I have already tested it on the new server and it works just great. We never got to the bottom of this and I don’t think we ever will now.

Image by weirdvis

 

Comments so far

poltava
November 2009

The problem is in leap seconds (http://en.wikipedia.org/wiki/Leap_second).
You can disable it by modifying Use_leap_seconds field in `mysql`.`time_zone` table.
Just execute this query:
UPDATE mysql.time_zone SET Use_leap_seconds = ‘N’;

Christopher Hill
November 2009

Wow, thanks poltava—I’ve never heard of leap seconds until now. Mystery solved.