How To Synchronize Your PHP and MySQL Timezones - Notes

PHP and MySQL are separate processes with their own default timezone configurations. You’re unlikely to run into timezone issues during development or if you’re deploying a single web application to your server. In that situation, PHP and MySQL can use the server’s time settings.

However, what if:

1. Your application can be set to the user’s timezone?
2. You’re running two or more applications with different timezone requirements?
3. Your application is installed on a shared server and you can’t set the default timezones?

Many developers solve the problem by shifting all date/time responsibility to PHP. They may also convert dates to Unix timestamp integers for simpler handling, although you should be wary of theY2K38 Bug.

While that can be a reliable solution, there are several drawbacks:

• You’re creating additional work for yourself.
• Your application requires additional processing to translate dates.
• It not easy to directly examine dates within a table.
• Some date-based SQL queries become difficult — if not impossible, e.g. return all records saved on a Monday.
• You’re unable to use MySQL date/time functions such as NOW().

Fortunately, you can synchronize PHP and MySQL timezones prior to updating or querying the database. Your application will require a single configuration variable which defines PHP’s timezone, e.g.

<?php
define('TIMEZONE', 'America/New_York');

We’ll use that to set PHP’s default timezone:

date_default_timezone_set(TIMEZONE);

What about MySQL? It’s possible to set its default timezone at the start of every session — I’d recommend doing it after you’ve connected to the database. The command is:

SET time_zone='offset';

where offset is a string value representing the difference to UTC/GMT, e.g. ‘-4:00′, ‘+3:00′, ‘+10:30′, etc. Note that the +/- sign is essential — even for zero — and timezone offsets are not necessarily a whole hour.

So let’s write a little PHP to parse the timezone offset and format a string MySQL can understand. First, we’ll create a new DateTime object, find the offset in seconds, and convert it to minutes:

$now = new DateTime();
$mins = $now->getOffset() / 60;

We can now calculate whole hours and minutes. The first line determines whether the offset is positive or negative, then converts the value to a positive number to make the calculation easier:

$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;

PHP’s sprintf function can then be used to format the string:

$offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);

Finally, we execute the SET time_zone command. The following lines provide an example, but you should use your own database library where possible:

$db = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpassword');
$db->exec("SET time_zone='$offset';");

The PHP and MySQL timezones are now synchronized within your application.