David Field  •  26 Aug 2017

MySQL, PHP and Timezones

Although it would be nice if we never had to deal with timezones, as developers it's usually necessary to convert dates and times into a local timezone for each of your users. Here are some tips and tricks when dealing with timezones in your PHP / MySQL apps.

Getting MySQL To Do It

MySQL can actually manage timezone conversions automatically. It will convert TIMESTAMP fields to UTC from your timezone when you insert the data, and convert back from UTC to your chosen timezone when you retrieve it.

By default, MySQL will use the timezone set on the host machine. This will typically be UTC and, unless you have a good reason to change it, we usually recommend leaving it as such. If you change the timezone of the host machine, then MySQL will pick this up automatically.

If you want MySQL to use a different timezone to your server, then you can change the time_zone variable in your MySQL configuration instead of changing your server's timezone.

One more option is that you can change the timzeone variable on a per-connection basis by using a query:

SET time_zone = <timezome>;

If the connected user has SUPER privileges, they can use SET GLOBAL instead, which will set the global timezone in the same way as the time_zone configuration variable.

Note that timezone settings only affect TIMESTAMP fields, whilst DATETIME and DATE both ignore the timezone. For example, if you have a TIMESTAMP field called created_at in a UTC database, and you then change the timezone of the database to New York, then the time for each of the created_at records will automatically display 5 hours earlier. By contrast, if you have a DATETIME or DATE field, then changing the timezone of the database will not change what MySQL displays - each record will remain exactly the same irrespective of the database's timezone.

MySQL also offers the CONVERT_TZ() function — if you need to do it manually — which you can use like this:

SELECT NOW(), CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');
-- 2016-10-21 08:42:00, 2016-10-21 17:42:00

However, it is up to you to make sure that the timezone tables MySQL uses to perform these conversions are up to date.

Query Offsets

You can use offsets in your queries instead of changing the server's variables. To do this, you’ll need to calculate the offset first. Here's an example using PHP's DateTime class:

$dt = new DateTime();
$dt->setTimezone(new DateTimeZone('Asia\Tokyo'));

$offsetSeconds = (int)$dt->format('Z');
// 32400 (seconds)

$offset = (int)round($offsetSeconds / 3600, 0);
// 9 (hours)

You can then use the above offset in your query by using the INTERVAL function:

SELECT NOW(), DATE_ADD(NOW(), INTERVAL 9 HOUR);
-- 2016-10-21 08:42:00, 2016-10-21 17:42:00

Doing It In Code

Of course, you can perform all your timezone handling directly in your code instead.

// SELECT NOW() as my_date_field;
$datetime = $record->my_date_field;
$dt = new DateTime($datetime);

$dt->format('Y-m-d H:i:sP');
// '2016-10-21 08:42:00+00:00'

$dt->setTimezone(new DateTimeZone('Asia\Tokyo'));

$dt->format('Y-m-d H:i:sP');
// '2016-10-21 17:42:00+09:00'

Just remember with this method that you won't be affecting the database at all; so while printing out a list of dates is going to work ok, it's not going to be correct if, for example, you're trying to group a list of dates by month in a query.

The End

Hopefully this has given you a overview of some of the ways you can interact with timezones using MySQL. As long as you're making sure to do sensible things like store all dates in UTC (or at least some other single timezone), only a little effort is required to make sure everything lines up correctly. If your data is not all in a consistent timezone, then, well… good luck!