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?
Add a comment
|
2 Answers
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"));
4 Comments
John
Right but what if I already have the timestamp in a different timezone? Can I convert it to a different timezone?
Pekka
+1 (and if I could, another +1 for
DateTime) but don't forget that for this to work, you need to know the mySQL server's current time zone to get the start date right! MySQL's time zone setting can differ from the system's zone.
moinudin
@Pekka and you can get that with
SELECT @@session.time_zone;
Phoenix
Also, if you didn't want to use the DateTime class (no reason why not, just saying) and you had a valid timestamp or date (like 5:00 PM, Jan 1, 2011, or 2011-01-01 20:00:00), you could do something like
date("whatever format",strtotime($timestamp) + $theoffset * 3600), where $timestamp is the date and $theoffset is the difference between the $timestamp's timezone and the timezone you're looking to change it to in hours.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
2 Comments
Pekka
Since PHP 5, PHP is the more sophisticated of the two: The
DateTime class fully supports time zones. A DateTime timestamp can be set and cross-converted to any zone. MySQL is unable to store timezone info in a DATETIME field at all. Still, CONVERT_TZ is a valid approach of course
Michał Klimczak
Not necessarily valid. Read Aarons response to this question stackoverflow.com/questions/1646171/…