This is an old post from 2009. As such, it might not be relevant anymore.
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.
Update: Discovered the problem not long after posting this. It relates to leap seconds, which you can read more about on the MySQL documentation page.