CODEWITHSUNDEEP
phpmysqltimezone
Saulius Antanavicius
Saulius Antanavicius

Reputation: 1381

Changing time zones and getting results from MYSQL based on that

I'm working on a new project which has a lot of different data and it's all stored in database with same time zone timestamp, East coast, I make sure the server time zone is switched to it before anything is loaded via 

date_default_timezone_set("America/New_York");

However I can't seem to wrap my head around it, I want to have a search function that allows a time zone selection, what if they select west coast time zone, should I only change the PHP part? Or should I query database differently? All the data is very time sensitive and it's proving very hard to wrap this around my head.

Any help or logic would be appreciated :P

Upvotes: 0

Views: 165

Answers (1)

Charles
Charles

Reputation: 51411

The main problem is that MySQL's support for timezones is laughable. Most other databases let you define the timezone as part of the datetime column, but not MySQL. Instead, they force you to do a connection-wide or server-wide timezone setting.

Your best bet is making sure MySQL stores all datetimes as GMT / UTC. You can enforce this by running SET time_zone = 'UTC' on connect. Do it at the same time as you set the character set. You are setting the connection character set, right?

PHP-side, you can then use a combination of DateTime and DateTimeZone to take the datetimes from MySQL and display them in the user's timezone. For example, let's pretend that we get the date 2012-11-13 14:15:16 from a MySQL DATETIME column. From the PHP interactive prompt:

php > $from_mysql = '2012-11-13 14:15:16';
php > $utc = new DateTimeZone('UTC');
php > $ts = new DateTime($from_mysql, $utc);
php > $pdt = new DateTimeZone('America/Los_Angeles');
php > $ts_pdt = clone $ts;
php > $ts_pdt->setTimezone($pdt);
php > echo $ts_pdt->format('r'), "\n";
Tue, 13 Nov 2012 06:15:16 -0800

As demonstrated, you just need to create the DateTime by expressly telling it you're UTC, if UTC isn't the timezone you've set using date_default_timezone_set. Switching the timezone of a DateTime is as easy as giving it a new one. I've used clone here to work on a copy. DateTimes are mutable, and it's sometimes easy to find yourself accidentally clobbering it.

Reverse date math works the same way, just transform their selection into UTC and run the math on the native numbers.

tl;dr:

  • Store and perform calculations on datetimes in UTC (GMT) only
  • Display all datetimes in the user's timezone only

Upvotes: 1

Related Questions