At Trellis Development we’re working on several applications which need to frequently convert dates between various time zones.

By default, both PHP and MySQL operate using a single timezone which is identified during installation and is quite often the same as the host server. Both tools allow you to specify a different timezone on the fly, and all following date operations work based off of that locale.

The primary goal is to preserve a base standard time so that we can easily convert between different timezones, without loosing the ability to convert again. Two choices emerged – storing a Unix Time stamp or a date string stored using GMT/UTC time.

The Unix Time stamp is a count of the number of seconds since January 1 1970 00:00:00 GMT.

The only difference between the two is that the time stamp is an integer so it makes no sense when read directly from the database. People don’t immediately understand that the date 1248988280 refers to 21:11:20 30-Jul-09 GMT. It makes more sense to me to store the dates in a format that’s easy to read, so we decided on storing GMT.

In the following demonstration, I show how to get the local time first and then convert that to GMT.

$base_time = time();

// print my local time
print date("H:i:s d-M-Y T", $base_time); // 14:49:49 30-Jul-2009 PDT

// Convert my local time to GMT
$gmt = gmdate("H:i:s d-M-Y T", $base_time);
print $gmt; // 21:49:49 30-Jul-2009 GMT

However, when developing the application it makes most sense to just generate the GMT date first. PHP has a great function for handling that called gmdate:

gmdate("H:i:s d-M-Y T");

At this point you can store that GMT format to your database. In MySQL, the supported date string format is the standard YYYY-MM-DD HH:MM:SS. The important thing is that you remember that the times are in GMT and will always need to be converted to local time for display.

Once you pull a record with the GMT date, you can then convert it to a specific timezone. The following example requires PHP 5.2 or above.

$cnv_date = new DateTime($gmt);
$cnv_date->setTimeZone(new DateTimeZone('America/New_York'));
print $cnv_date->format('H:i:s d-M-Y T'); // 17:49:49 30-Jul-2009 EDT

Now, we have a standard date string in GMT stored in the database, and we can easily convert it to another timezone without changing the entire server timezone.

I would recommend that you wrap this functionality into a basic object within your application. Although it’s not much work for each conversion, it would further reduce the amount of code and give you a central point of management.

Possibly related posts:

1. Missing mysql.sock on Mac OS X
2. Install Apache, MySQL, and PHP on OS X (10.5)
3. MySQL Backup Shell Script