MySQL, PHP and time conversion

Another post of utter geekery with some highly uninteresting and very unnecessary programming tidbit (I bet you can’t tell I’ve been coding my week-end away, can you). Normal people, please skip over.

When working with MySQL and PHP, clean time conversion can become a real pain in the arse.

You ought to always store your timestamps as GMT in your DB, no matter what.
Problem is, when you feed the text value returned by MySQL into PHP strtotime(), it is not read as a GMT time! Check it by converting it back to a text format (using strftime()): you’ll see it doesn’t match the original GMT time such as returned by MySQL.

To make all this work, you simply need to ensure it is read as GMT by PHP by appending the timezone format to the string returned by MySQL.

An example:


echo $my_sql_gmt_time; // 2005-03-13 15:00:12

echo $bad_ts = strtotime($my_sql_gmt_time); // 1110747612 < - bad echo strftime($bad_ts); // 15:00:12 CST <- bad echo $good_ts = strtotime($my_sql_gmt_time . " GMT"); // 1110726012 <- good echo strftime($good_ts); // 09:00:12 CST <-> 15:00:12 GMT < - good

This very stupid little glitch (why would PHP assume CST timezone when most functions use GMT?) took me twenty minutes to debug, despite my semi-thorough reading of the PHP doc for the relevant functions, so I figured I’d spare the time to the next fellow google searcher.

7 comments

  1. Heres a small tip to save you headaches that I use. Since mysql has its own format for storing time I don’t set up the table as a date value I simply set it as an interger a number stored on the DB.

    Makes it all real simple you get the timestamp with time() store this number in the DB when you want to use it do this :

    echo $time=date(“d-M-Y h:i:s”,array[‘name_of_your_row’]);

Leave a Reply