I’ve recently had the displeasure of working with timezones on my main project at work. I work on an internal administrative system for Buffalo Studios. It has all sorts of functionality, such as content editing tools, customer service tools, reports, graphs and more. Its sole purpose is to make the lives of our internal customers easier, as well as to provide some functionality that doesn’t belong in our game environments.

When my project (let’s call it Admin) initially started, the servers it was running on were all in the America/Los_Angeles time zone. Everything from Apache to MySQL, all in America/Los_Angeles. A few databases Admin connected to were in one other time zone – UTC. When Admin was first built, it was built using the initial version of our internal PHP Framework. This framework had no knowledge of timezones and the like, since it was designed for our games, which are all run on UTC and don’t have to worry much about timezones.

With this in mind, Admin had a small utility to convert UTC dates over to America/Los_Angeles. Any date coming out of these alternate databases were stored in UTC, so a quick PHP conversion for display and I was good. Until recently.

The most recent Daylight Savings Time switch this fall caused some date & time problems for us. Not all of our servers were on UTC, and the fact that the same hour happened twice in a row caused some problems for some of our automated scripts. The decision was made to switch Admin’s servers on over to UTC at our earliest convenience. I didn’t think this would be a big problem, but it sure turned out to be.

The first thing I needed to do was to switch my local MySQL database to UTC for testing purposes. We were going to leave the app itself in America/Los_Angeles, because that’s the timezone that the vast majority of Buffalo operates in. So the system would be UTC and the app would display dates in America/Los_Angeles. That meant that all dates coming out of Admin’s database, after the switch to UTC, needed to have the timezone conversion done to them.

At first, I made this change within PHP itself. The funtionality that allowed me to easily say “this date is UTC, convert it” was extended to all dates coming out of the Admin database. Unfortunately, this posed a problem on our QA environment. QA Admin was still running in America/Los_Angeles, because it wouldn’t be converted to UTC until after our upcoming code release. So dates were borked in the meantime with the app assuming that the source data was in UTC.

Normally I would have just switched the QA server to UTC, but that’s not possible for us yet. QA Admin resides on the same machine as Dev Admin… As well as Production Admin’s MySQL database. So I couldn’t make that switch because it would adversely impact the Production database (this is being remedied early next week – Production Admin’s database will be split to its own dedicated set of servers!). So I figured the dates would just be temporarily borked during testing.

But testing couldn’t be performed properly because there were new scheduler features in some of Admin’s functionality. Being able to delay certain actions until later, for example. So how could the QA department properly test this date-centric functionality? Additionally, what if we needed to make timezone switches again?

I was willing to write these problems off, until I got one more request that made me re-evaluate how Admin was handling dates. The head of our IT department requested the ability for users to be able to set their timezone. We have some users around the world, so it makes sense for them to be able to view dates in their timezone. Thus… I had to rework the way that Admin handled dates.

The first step was for the app to automatically set the default timezone upon load. This was easy:

ini_set('date.timezone', 'America/Los_Angeles');

The next step was to have the user’s timezone preference set as the default as early as possible. This was accomplished in much the same way. We’d select the user’s timezone out of the database and then run the same PHP function above with that result.

One thing I wanted to use was the ability to tell MySQL what my connection’s timezone was. MySQL has the ability to set the system and connection timezone. If you set your connection timezone to something other than the system timezone, then dates that are selected from MySQL are automatically adjusted to your connection’s timezone. This was accomplished by doing the following, in PDO after the connection was established:

$statement = $pdo->prepare('SET time_zone=?');
$statement->execute(array(
    date_default_timezone_get()
));
$statement->closeCursor();

I’m using date_default_timezone_get() because the database connection could be made at any time, after a possible timezone switch. The thing is, though, is that the database connection to get the user’s timezone has already been established. Once I’ve retrieved that timezone, I basically need to set the timezone for any active database sessions:

foreach (self::$databases as $database)
{
    $statement = $database->prepare('SET time_zone=?');
    $statement->execute(array(
        $timezone
    ));
    $statement->closeCursor();
}

The next problem I ran into: not all of our databases are MySQL. Not all of them support setting the timezone. Thus, I still had to backup on converting the dates out of databases to the app’s timezone. Can’t win them all, right? But I had to rework the way the timezone conversion ran. We had a convenience function that did timezone conversion and formatting for output, all-in-one:

static public function dateTimeFormat($timestamp, $isUtc = true, $format = self::DEFAULT_DATETIME_FORMAT)
{
    if ($timestamp instanceof DateTime)
    {
        $dateTime = $timestamp;
    }
    else
    {
        $dateTime = new DateTime;
 
        if (is_scalar($timestamp))
        {
            if (!preg_match('#^[0-9]+$#', $timestamp))
            {
                $timestamp = strtotime($timestamp);
            }
 
            $dateTime->setTimestamp($timestamp);
        }
    }
 
    if ($isUtc)
    {
        $localTimezone = new DateTimeZone('America/Los_Angeles');
        $dateInterval = DateInterval::createFromDateString($localTimezone->getOffset($dateTime) . ' seconds');
 
        $dateTime->add($dateInterval);
    }
 
    return $dateTime->format($format);
}

The problem here is that it assumed America/Los_Angeles for the target timezone… And only allowed conversion from UTC. Well, we now needed to support all target timezones. This function was used a lot in Admin, so it took me a long time to make the conversion over to the following functions:

static public function dateTimeFormat($timestamp, $format = self::DEFAULT_DATETIME_FORMAT)
{
    if ($timestamp instanceof DateTime)
    {
        $dateTime = $timestamp;
    }
    else
    {
        $dateTime = new DateTime((preg_match('#^[0-9]+$#', $timestamp) ? '@' : null) . $timestamp);
    }
 
    return $dateTime->format($format);
}
 
static public function convertFromTimezone($timestamp, $fromTimezone)
{
    if ($timestamp instanceof DateTime)
    {
        $dateTime = $timestamp;
    }
    else
    {
        $dateTime = new DateTime((preg_match('#^[0-9]+$#', $timestamp) ? '@' : null) . $timestamp, new DateTimeZone($fromTimezone));
    }
 
    $dateTime->setTimezone(new DateTimeZone(date_default_timezone_get()));
 
    return $dateTime;
}
 
static public function convertToTimezone($timestamp, $toTimezone)
{
    if ($timestamp instanceof DateTime)
    {
        $dateTime = $timestamp;
    }
    else
    {
        $dateTime = new DateTime((preg_match('#^[0-9]+$#', $timestamp) ? '@' : null) . $timestamp);
    }
 
    $dateTime->setTimezone(new DateTimeZone($toTimezone));
 
    return $dateTime;
}

With these functions, I could properly take any date out of a non-timezone-supported database and convert to the app’s timezone. Additionally, if I needed to insert to these databases, I could convert to their timezone on the way in, as well. The only problem is that any of those databases could just change their timezones in the future, and it would take a bit of time to convert all of the code over if that happened. My next step on that problem, that I have yet to do, is to define constants associated with those databases so that I can reference the constant and only change it once if I ever have to. Or, I could build a way to make that conversion in our modeling systems.

One last problem I dealt with once I started using these functions – DateTime()’s constructor does indeed accept a Unix timestamp. Unfortunately, I had problems with it staying in the proper timezone. I didn’t read 100% of the DateTime() documentation on PHP.net, and thus missed a point where using a Unix timestamp to instantiate a DateTime() always results in the DateTime() setting itself to the UTC timezone. Something else to keep in mind.

So that’s it, right? No, wrong again. MySQL only does conversions on TIMESTAMP columns. DATE, TIME and DATETIME are not supported. And wouldn’t you know it, one of our games’ legacy tables all use DATETIME. So I couldn’t use automatic conversion for those anyway. All that work I put in on these three new functions would be fruitful for having to continue manually converting some dates even out of a MySQL database.

Before you get your hopes up that my odyssey wrapped up there… Don’t. I’m using Gentoo Linux for my local development machine, and everything had been handled for me when I installed MySQL. But Admin is running on Ubuntu servers, and for some reason the default Ubuntu configuration of MySQL does not include automatic installation of timezone support in MySQL. After some research online, I found that I had to manually populate MySQL’s timezone data, like so:

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

Before I did this, I got errors when attempting to set MySQL’s timezone in the connection:

SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: ‘America/Los_Angeles’

After importing from zoneinfo, I was able to set the timezone on the connection. Yet another headache. But, thankfully, once that was done, I was set! I’d handled everything I needed to, so far, to make this conversion work. And these changes all now work on our Dev/QA servers because it doesn’t matter now what timezone MySQL is in for Admin. It automatically converts the dates on the way out. Since it’s a database I have 100% control over, I was able to make sure all dates were defined as TIMESTAMPS. Actually, the Admin database is the only one with the connection timezone set. Our other MySQL databases are outside my control and they do not have the timezones installed. So I have to manually convert anyway, in PHP.

Something to note, though, is that Admin isn’t fully using DateTime() in the way that I want it to be. It would take a lot of effort to make the full conversion, and that’s time I just don’t have. I had to take an intermediary route instead, splicing the style it used to have with a kind of bridge. If I had to do it over again, I’d do things properly from the get-go.

So here are my recommendations for building a PHP+MySQL app that needs to support timezones. In fact, I’d recommend doing this anyway. It’s good practice, especially if your app will be dealing with dates at all.

1. Use DateTime() whenever you’re dealing with dates. Don’t use time(). Don’t use date(). Just say no to Unix timestamps in PHP if you can help it.
2. Make sure MySQL’s timezones are installed and always set the connection timezone.
3. Always set PHP’s default timezone at the app level, then allow user’s to select their own timezone – this should set both PHP’s timezone and the MySQL connection’s timezone.
4. If you have to manually convert timezones in PHP, associate the source of the data (i.e. a database) with its known timezone. For example, if you have a named database, something like this: const DBNAME = ‘America/New_York’;
5. Always use TIMESTAMPs in MySQL. You’ll probably want a DEFAULT value, which would be ’0000-00-00 00:00:00′.