4

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?

John
  • 9,840
  • 26
  • 91
  • 137

2 Answers2

5

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"));
moinudin
  • 134,091
  • 45
  • 190
  • 216
  • Right but what if I already have the timestamp in a different timezone? Can I convert it to a different timezone? – John Jan 01 '11 at 09:31
  • +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. – Pekka Jan 01 '11 at 10:19
  • @Pekka and you can get that with `SELECT @@session.time_zone;` – moinudin Jan 01 '11 at 10:25
  • 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. – Phoenix Jan 01 '11 at 11:02
3

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

spoulson
  • 21,335
  • 15
  • 77
  • 102
  • 1
    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 – Pekka Jan 01 '11 at 10:15
  • Not necessarily valid. Read Aarons response to this question http://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra – Michał Klimczak Apr 02 '12 at 18:20