Wrangling timezones in PHP, MySQL, and Yii

Not to mention a dose of problems with hosted database solutions, yay hooray!

One of the key features of Mantis, actually, the whole product, relies on keeping track of time. Specifically, the time an employee clocks in and the time they clock out. If I'm going to be keeping track of hours worked, that little bit of information would be key, you'd think.

You'd be right.

Best Practices

There are a few best practices with dates and the PHP/MySQL stack. One of the biggest is always store times in the database as UTC/GMT (+00:00). You want to have a baseline, something against which you can always define truth. In this case, I'm always certain that times in the database are timezone agnostic, i.e. they are always GMT. Timezones are messy, this gives me a warm fuzzy feeling that I at least know one thing is true. It goes downhill from here.

This means that whenever I get the data out of the database, I'll have to convert it to local time in PHP, ie: at the last possible moment. (Another best practice: convert it at the very end.)

The Process

In Mantis, we allow users to define a timezone for their company (eventually we'll allow timezones/team). This is another bit of truth I have, thank goodness. I don't have to ask the server what timezone we need to use, which is great because it will often lie. Since I know have two bits of truth, (that all database times are GMT and all times displayed are a user specified time we'll call CST) I can start doing some interactions.

Putting the Data In

To put the data into the database, all you have to do is use the UTC_TIMESTAMP() MySQL function. This will give us exactly what we want: the current time in GMT. In Yii, that looks like this:

$model->in_time = new CDbExpression('UTC_TIMESTAMP()');

That sets up a query to run such that when it is inserted, MySQL will evaluate the UTC_TIMESTAMP() to the current GMT time.

Getting the Data Out

Getting the data out, you'll need to convert it to the user's timezone. When the user logs in, I store the user's timezone in a session variable. To properly convert the date, use PHP's glorious DateTime object. The DateTime object has a parameter that lets you define the timezone and a method that lets you change it. How convenient!

// gets the local timezone out of the session
$local_timezone = $this->getLocalTimezone();
 
// create a new DateTime object with the time from the database
// and the timezone of GMT
$date = new DateTime($gmttime, new DateTimeZone('GMT'));
 
// translate to the user's timezone
$date->setTimezone(new DateTimeZone($local_timezone));

The first thing to do is get the timezone of the company (we'll talk about where those come from in a bit). Then, since I know that the data came out as GMT, I set the second parameter to a GMT timezone object. The last thing you have to do is translate it to the local timezone by calling the setTimezone method.

Comparions and Calculations in MySQL

We've covered the PHP side of things, but sometimes we want to perform aggregation calculations in MySQL rather than pulling it out and looping it in PHP. For example: Mantis offers a report that displays hours worked by day across the entire company. If I was to just group it by day in the database, I'd end up with a sum of hours per GMT day, rather than local day. That's no good. We've got to get those times into the local timezone.

To convert the date in MySQL, use the CONVERT_TZ() function in MySQL. CONVERT_TZ takes three parameters: the time, the current timezone, and the desired timezone. For the current timezone and desired timezone, you can either pass GMT offsets (-05:00) or named timezones. Use named timezones. Named timezones give you the huge benefit of taking care of daylight savings for you, not to mention all the other strange discrepancies throughout history, of which there are many. Here's how that part of the query looks in my code:

DATE_FORMAT(CAST(CONVERT_TZ(`in`,"GMT",:mysql_timezone) AS DATE),\'%m/%d\') as `date`

Working from the inside out, the first function called is CONVERT_TZ and pass in the mysql_timezone (covered momentarily). This converts the time from GMT to local. After that, I just cast it as a date and format it as MM\DD for grouping. Now we have a query that returns a sum-by-day on local days rather than GMT days.

Named Timezones

Named timezones cover a multitude of sins, daylight savings primarily. We need to get a hold of those names.

PHP

PHP's named timezones are pretty simple to implement, and they have a complete referenced list on their site: http://php.net/manual/en/timezones.php.

MySQL

This one is a little trickier. To use named zones in the CONVERT_TZ function, you have to have several tables set up properly in your installation of MySQL. I didn't have any of those tables... of course. Why should things be easy?

(Further reading: http://dev.mysql.com/doc/refman/5.0/en/time-zone-upgrades.html)

There is a utility to install those tables, but seeing as I wouldn't have the same level of access to that on my database-as-a-service provider, I didn't want to go that route. Thankfully, the MySQL docs site offers those tables for download. Download those tables and add them to your mysql database and your CONVERT_TZ function will magically start working properly.

Making Them Work Together

To link up the timezones across both PHP and MySQL, I've set up a table that has the PHP name, the MySQL name, and a user-friendly name to display to the end user. When the user selects their timezone, we automatically have the right PHP and MySQL names for that zone.

Database as a Service Woes

Once I finally figured out how to do everything on my local machine, it was time to take it to my staging environment to see if I could get it up and running there. Obviously I couldn't. I think I've mentioned before, either here or on my podcast that I had settled on ClearDB as a hosted MySQL solution. I've been pretty happy with them up until last night. I queried my database to see if they had the time_zone tables installed and was greeted with the happy answer of: NO. I couldn't even get at the tables. I emailed support and asked if there was any way to add those tables or give me access to them, and the reply was:

Thanks for reaching out. After looking up your account with us via your email address, I found that your ClearDB database is running on our multi-tenant platform. Unfortunately, access to the mysql database (including timezone tables) is restricted on our multi-tenant clusters. We apologize for any inconvenience that this may cause. We do however support timezone capabilities via MySQL for our dedicated cluster customers. Please let us know if you would like to have a conversation about moving your existing ClearDB database over to a dedicated ClearDB cluster.

Fine then, let's look at the dedicated cluster. FOUR HUNDRED DOLLARS a month. No way I'm paying $400 for timezone support. Heroku has another hosted MySQL add-on: xeround. With xeround, I can deal with those tables directly... for free. Problem solved.

Hopefully this will help you avoid hours of searching and struggling, trying to wrangle timezones. Feel free to leave any corrections/questions below!