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:12echo $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.
“Normal people, please skip over.” I like it. I’m going to steal that, okay?
Thanks for all the code! I just started with WordPress, PHP and MySQL and you might have already seen my first endeavor. Now, if I only had a good idea for a plugin…
Aha! That’s why the “Dunstan’s time since” plugin doesn’t work exactly like it should!!!
Wow nice script! What i am looking for………..
Thanks for the code!
Thanks so much for posting this. It saved me some ‘GMT time’.
Thanks for the code… It saved me the time and greatly reduces the Headche…
Thanks for the script
Its very nice but if that would have done by showing the result as age would be appreciable.
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’]);