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:
- 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.
- 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. - 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.
I admit I’ve never thought about leap seconds and how different platforms could take them into account in their implementation. So far my practice to avoid problems in similar circumstances has been that of addressing a problem (IE getting a timestamp) always through the same tool (IE PHP); this has helped reducing both dependency and potential conflicts (such as the one you mention) between different application layers. At any case, I’m sure that there are situations where such an approach isn’t feasible, so thanks a lot for sharing this interesting piece of info, that I’ll surely keep on mind. Keep up with the good work! 😉
I also never had such a problem as I usually use UNIX_TIMESTAMP and FROM_UNIXTIME to read/wrie dates. I caught it with the project where some module had to write dates to MySQL format directly while others still use UNIX_TIMESTAMP and FROM_UNIXTIME.
The confusion gets worse if there is ever a need to give a count of the number of seconds which have elapsed according to legal statues. See this page on epoch time.
i was searching for it from long back i got it for my project