Why MySQL timestamp is 24 seconds different from PHP

You may find the timestamp value returned by MySQL UNIX_TIMESTAMP() function 24 seconds grater than that returned by PHP functions and methods like strtotime(), mktime(), DateTime::getTimestamp(), Zend_Date::getTimestamp().

For example, this code outputs timestamps for the same date/time 2010-10-20 0:00:00 UTC:

$db = new MySQLi(<em><your connection settings></em>);
$rs = $db->query("SELECT UNIX_TIMESTAMP('2010-10-20 00:00:00 UTC')");
$rec = $rs->fetch_row();
echo 'UNIX_TIMESTAMP: ', $rec[0], "\n";

echo 'mktime: ', mktime(0, 0, 0, 10, 20, 2010), "\n";

echo 'strtotime: ', strtotime('2010-10-20 00:00:00 UTC'), "\n";

$dt = new DateTime('2010-10-20 00:00:00 UTC');
echo 'DateTime::getTimeStamp: ', $dt->getTimestamp(), "\n";

require_once 'Zend/Date.php';
$zd = new Zend_Date('2010-10-20 00:00:00 UTC');
echo 'Zend_Date: ', $zd->getTimestamp(), "\n";

If you set UTC as a default timezone for both PHP and MySQL then the example code above will output:

UNIX_TIMESTAMP: 1287532824
mktime: 1287532800
strtotime: 1287532800
DateTime::getTimeStamp: 1287532800
Zend_Date::getTimeStamp: 1287532800

So, why only MySQL is 24 seconds too fast?
The cause is a correction for leap seconds applied by MySQL but not by any of the above PHP function or class. In fact, the assumption that day is 86400 seconds doesn’t work anymore as Earth rotation is slightly slowing down. Therefore, a leap second is introduced from time to time to keep atomic clocks in sync with the solar time.

Well, this is very curious. But for us, developers, this only adds a serious potential incompatibility as a lot of code is written in assumption that day is 86400 seconds exactly as well as PHP and MySQL timestamps have to be equal for the same date.

There are 3 ways you can avoid incompatibility problems:

1. Disable leap seconds correction in MySQL timezone configuration table:

   UPDATE mysql.time_zone SET Use_leap_seconds = 'N'
   

   But this is not a good way if you’re going to distribute your code to other servers.

2. Convert dates to timestamps and vice versa in PHP only. Avoid using MySQL UNIX_TIMESTAMP() and FROM_UNIXTIME() functions and use functions like strtotime() and date() on PHP side instead.
   But this will require additional processing for each row returned by MySQL queries or sent to MySQL which is not always acceptable.
3. Another solution is to avoid sending dates as strings to the database and always use UNIX_TIMESTAMP() and FROM_UNIXTIME() to read and write date/time values. Unfortunately this solution doesn’t work with DATE fields as they are truncated and this will make date/time values look different if you access the database directly, for example with administration tools.

Important update: I realized  that solution #3 doesn’t work for DATE fields and may cause confusions when using DB administration tools. So now I recommend solution #2 although it requires additional data processing on the application side.