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.