MySQL is 24 seconds too slow

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:

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

Update: The problem relates to leap seconds, which you can read more about on the MySQL documentation page.