PHP/MySQL timestamp and timezones

Question

If I grab the current timestamp using the NOW() function in MySQL can I grab that field via php and give that time in different time zones? Basically converting current time in the current timezone to another timezone?

Answer 1

You can use the DateTimeZone class:

$gmt = new DateTimeZone("GMT");
$datetimeInGMT = new DateTime($now, $gmt);

It also takes locations in the form continent/city, e.g. Europe/London.

If your datetime is non-UTC, you can use setTimezone:

$datetimeInGMT = new DateTime($now, new DateTimeZone("America/New_York"));
$datetimeInGMT->setTimezone(new DateTimeZone("GMT"));

Answer 2

MySQL's timezone handling is much more sophisticated than PHP's. It handles simple timezone (EST, PST, etc) and what i will call 'regional timezones' (America/Eastern). Using regional zones uses proper conversion including daylight savings time, even when DST rules have changed over the years.

What I've done is store all my datetimes as UTC using MySQL function UTC_TIMESTAMP(). Then, in queries, I use MySQL function CONVERT_TZ() to my target timezone. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Note: you may need to update your timezone tables. http://dev.mysql.com/downloads/timezones.html