How To Synchronize Your PHP and MySQL Timezones

Contributing Editor

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 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:


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.

tip: What’s with the backslashes?

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.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Josh Johnston

    You could also use:

    format(“P”);
    ?>

    From the manual for date formats:
    P Difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3) Example: +02:00

    • Josh Johnston

      Sorry filter ate the php tags …

      $dt = new DateTime();
      $offset = $dt->format(“P”);

      • http://www.optimalworks.net/ Craig Buckler

        Thanks Josh. Well spotted – that’ll reduce the code even further.

      • Anonymous

        thanks. I was looking for a long time for this.

  • Qtronik Webmaster

    haaaaa right in time blog post !

  • Jakub Vrana

    Good morning. You can use date(‘P’).

  • http://www.brothercake.com/ James Edwards

    For web-applications, this is the wrong solution (IMO) :)

    You shouldn’t attempt to cater to user timezones using server-side code — if you produce timezone specific output, the end result is not cacheable.

    You should do all server-side date manipulation in GMT, and convert to user-locale on the client http://blogs.sitepoint.com/simple-date-and-time-localization-with-javascript/

    • http://www.optimalworks.net/ Craig Buckler

      I’m not sure I understand your point?

      This would primarily be used for a web application — say a stock control system. In that situation, you probably want to set a default timezone for the whole app (not per user). In addition, cached results are not desirable if the inventory changes frequently — although it’d be easy enough to implement if you required it.

      Coding is far easier if PHP and MySQL are handling dates in the same way. There’s no need for date conversion code and SQL queries can be optimized accordingly.

      • http://www.brothercake.com/ James Edwards

        My point is — surely if you always work in GMT, you never get timezone conflicts between PHP and MySQL?

      • http://www.optimalworks.net/ Craig Buckler

        I’m making the same point. Unfortunately, you cannot assume PHP and MySQL are set to the same timezone. Both are separately configured, may not inherit the server’s time, and are not necessarily on the same server anyway.

        This method ensures you can set a single matching timezone (such as GMT) in both processes within one web application.

      • http://www.brothercake.com/ James Edwards

        Yeah but surely, the real value of GMT is that it *isn’t* a timezone, it’s an absolute. Surely it doesn’t matter what timezone the servers are set to, GMT is GMT.

      • http://www.optimalworks.net/ Craig Buckler

        PHP and MySQL cannot necessarily reference GMT.

        There are a few ‘gm’ date functions in PHP but they’re limited. PHP5′s DateTime object and all other date/time functions will default to the the timezone set in PHP’s configuration.

        Similarly, MySQL will use the timezone set in it’s configuration — and that server could be on the other side of the world. MySQL has three UTC functions, but several dozen other date/time functions assume the local timezone.

        Therefore, if we’ve used NOW() in MySQL it could be wildly different to time() in PHP. You’d always want them to be similar values — or at least within a few minutes of each other.

        The solution above sets the same timezone for PHP and MySQL so they are synced within the web application. Any PHP or MySQL date/time function can then be used safely — assuming you’re happy with a few seconds difference here and there.

        Further to this, setting GMT rather than your actual timezone limits some SQL functionality. For example, fetching all records created on a Monday is easy in MySQL: SELECT * FROM table WHERE DAYOFWEEK(timestamp) = 2;

        However, GMT’s ‘Monday’ could be different to your timezone. You’d need to implement some convoluted PHP code or more complex SQL queries which apply an offset before any date is handled.

      • http://www.brothercake.com/ James Edwards

        Yeah gmdate is what I always use – never found myself unable to do anything; you can give it a timestamp reference to create date objects at particular times.

        But then again, I do very much architecture applications to offset most time and date processing to the client; I guess I’ve never had problems with gmdate because I’ve never tried to do anything particularly complex with it. And I’ve never seen the point of SQL date functions, when the host language(s) have everything you need already. But again, that probably belies my lack of experience with SQL as much as anything else!

        nb. for your “monday” example, I would engineer that as a date-range from x to y, where x and y are absolute timestamps; then alias that in the client interface such that, when they select “monday” they’re actually selecting “3pm sunday to 3pm monday”, or whatever.

      • seza

        Totaly agree with James. SET time_zone only apply conversion on timestamp field, date, datetime and time are not converted when a mysql client retreive data.

        From mysql 5.0 documentation : ” The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. ”

        If you use multiple local timezone (eg. work with two or more php server, local timezone setting for website client…); the simplest way is to store all your data in GMT format. This assure the same processing for all date data, and data consistency in database.

      • http://www.optimalworks.net/ Craig Buckler

        Remember that this isn’t about an application which supports multiple timezones (where GMT/UTC could be preferable) — it’s about a web application being set to a single timezone no matter where its users reside.

        In that situation, it makes sense to work in the timezone you require and use the tools PHP and MySQL offer. There’s no need to worry about date/time conversion or accidentally using the wrong function.

  • Hamish Durkin

    That is almost psychic. Yesterday I needed this exact information, being SET time_zone=’offset’. I should note that it didn’t work for me at first (returned an unknown timezone error) until I ran the following command on my server (Cent OS 5):

    mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

    Thought I’d post it here in case others were having the same problems as me.

  • David

    Unless I’m mistaken, this doesn’t solve the issue with daylight savings changovers not being in sync between MySQL and PHP. Yes, both systems might be using the same timezone, that won’t help you if one thinks your on daylight savings, and the other doesn’t. Oh, how I loathe DST…

  • Rory

    See this post on why this method of setting the offset just isn’t good enough:

    http://derickrethans.nl/storing-date-time-in-database.html

    • http://www.optimalworks.net/ Craig Buckler

      This solution is ideal if your application is set to one single timezone and you need PHP and MySQL to be in sync.

      It’s not a solution for storing or calculating datetimes based on users in two or more differing timezones. That will certainly lead to issues — especially with DST. In that case, use the solution above to store all datetimes in GMT/UTC (or whatever base zone is practical). Then convert to/from the user’s timezone when analyzing input or presenting results.

  • Anonymous

    I’m using

    date_default_timezone_set(TIMEZONE);
    +
    function _datetime($datetime) {
    return date(‘h:i A d/m/Y’, strtotime($datetime) + date(‘Z’, time()));
    }