Time Zone conversion using MySQL

The Need: Time Zone Conversions

I needed to put together a solid way to convert time zones and found it was hard with standard tools.  We have clients all over the world and each needed to be able to view a date (webcast, live meetings, etc) in their specific time zone.  There are a lot of date conversion tools around for PHP, but when I wrote this, I had trouble getting them working as I wanted.  Most of the conversion libraries available for me at the time didn't work well for one reason or another and I wanted to go as Javascript-lite as I could.

I discovered a solid time-zone date conversion tool right in my relational database -- MySQL.  You have to do a little installation, but afterwards, just feed it a little query and you have your conversion.

select DATE_FORMAT(CONVERT_TZ(Now(), 'US/Central','US/Pacific'),'%m/%d/%Y') as date

or

select UNIX_TIMESTAMP(CONVERT_TZ(Now(), 'US/Central','US/Pacific')) as date

You can use them as a stand-alone to just get the current time, or you can use them as part of an overall query, which is much more common.

I display the dates for the user to pick from in a drop down.  I populate the drop-down using data from an array that looks like this:

$time_zones = array (
    'US/Central'     => 'Chicago',
    'US/Eastern'    => 'New York',
    'US/Pacific'     => 'Los Angeles',
    'US/Mountain'  => 'Denver'
    );

foreach ( $time_zones as $tz => $tz_name)
{
    $q     = "select UNIX_TIMESTAMP(CONVERT_TZ(Now(), 'US/Central','{$tz}')) as date";
    $date = mysql_result ( $r, 0, 'date' );
    $time_zones[$date]=$tz_name;
}

The variable $time_zones gets fed to the select tag to build the dropdown.  "US/Central" is where my servers are; change it to wherever yours are (or, more proper, whatever the server time zone is set to).

Setting up the Server

To use the data, you have to set up your mysql server.  I've done this a few times and it's mostly painless, but you may end up downloading a few programs to get it all working.  I have several load-balanced database servers at work so I had to do this for each of them (the master and the two replicators).

Log in as root, then do

> up2date tzdata

as normal user:

> mysql_tzinfo_to_sql /usr/share/zoneinfo/ > timezone.sql

then (you'll need to use whatever user that has the rights to do this; I use "root" in this example -- also, you'll need to know the proper password):

> mysql -uroot -p mysql < timezone.sql

> service mysqld restart

Login to the mysql monitor:

> mysql -uroot -p mysql

From the monitor prompt do (where username and password are the credentials to log in via your PHP scripts)

> grant select on mysql.time_zone to username@localhost identified by 'password';

> grant select on mysql.time_zone_leap_second to username@localhost identified by 'password';

> grant select on mysql.time_zone_name to username@localhost identified by 'password';

> grant select on mysql.time_zone_transition to username@localhost identified by 'password';

> grant select on mysql.time_zone_transition_type to username@localhost identified by 'password';

Now that user only has access to select information, but can't update it.  Every now and then you should check for updates to the time zone information and re-import the data.

Conclusion

There are probably more ways to do this now, but this has worked for me flawlessly for the last few years.  If you can't find a reliable solution but have access to a MySQL server with the time zone database set up, this one will work great for you, too.

Contact me here.

comment on this post on twitter