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:
- Your application can be set to the user’s timezone?
- You’re running two or more applications with different timezone requirements?
- 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 the Y2K38 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:
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:
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.
You may have noticed the initial backslash when creating instances of the DateTime and PDO base class. These may be necessary if you’re using namespaces within your PHP application. You don’t need to remove them if you’re not.
Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.