Translating Geek Into English

I’m currently working on the fourth edition of my “PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide” book, due out later this summer. In it, I discuss how to effectively use MySQL’s support for time zones. Specifically, there’s an argument to be made for storing dates using Coordinated Universal Time (strangely enough, abbreviated UTC). UTC is commonly the same time as Greenwich Mean Time (GMT), except during the summer when the UK is an hour ahead of UTC. And that’s the point of using UTC: storing times (and therefore dates) in a format indifferent to any particular time zone. By comparison, my forum software or my blogging software may likely be storing times using the local time zone for the server. That may not seem like a problem until I move the site from one server to another in a another time zone, thereby introducing a disparity in the times. How critical of an issue is that? Well, it depends upon the application, but there’s an easy fix: storing times and dates in UTC.

As I write in the book, three things are required to use UTC. You do not have to change anything about the column that’s storing the date and time, but you do have to change how you INSERT or UPDATE records. Instead of using the NOW() function to get the current timestamp, you’ll want to use UTC_TIMESTAMP(). You can alternatively use UTC_DATE() and UTC_TIME(), to just return the current UTC date or time, respectively. That’s the first step.

Presumably, even though the times are being stored as UTC, you’ll probably want to present them using another time zone. (In the book the specific example converts all times to the user’s selected time zone.) To convert from one time zone to another, use MySQL’s CONVERT_TZ() function. Its first argument is the date and time being converted; the second argument is the original time zone; and, the third argument is the destination time zone. For example, to convert the posted column from UTC to Sydney, Australia’s time zone, you’d use CONVERT_TZ(posted, ‘UTC’, ‘Australia/Sydney’).

The third thing you’ll need to do is install time zone support in MySQL. In other words, the MySQL database needs to be notified of all the time zones that exist and what their offsets are (from UTC). If you don’t take this step, the CONVERT_TZ() function will return NULL values for every conversion. How you install the time zones depends upon your OS. If you’re using Windows, you’ll want to download the time zone description tables from MySQL’s Web site. The download contains the files that correspond to the time zone tables. You just need to shut down MySQL, unzip the downloaded files, and copy/move them to the folder that represents the mysql database.

If you’re using most versions of Unix, including Mac OS X, the operating system itself has time zone definitions built in, and those should be used to populate MySQL’s tables. The most likely location of the system’s time zone files is /usr/share/zoneinfo. After you’ve confirmed the directory exists, you can populate the time zone tables in the database by executing the mysql_tzinfo_to_sql script that comes with MySQL:

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

That would be executed from a command line interface, of course, and you’d need to enter the (MySQL) root user password when prompted.

When I executed this command on my Mac, I saw an odd error:

Warning: Unable to load ‘/usr/share/zoneinfo/+VERSION’ as time zone. Skipping it.

What the what? The problem occurred because in the zoneinfo folder, the very first file, named +VERSION, indicated the zoneinfo version. This, of course, was not a time zone file, so when the MySQL script encountered it, it didn’t know how to handle the file and therefore just terminated the import. After deleting (or temporarily moving) the +VERSION file from the zoneinfo directory, I was able to install the time zones in MySQL without a problem. Well, actually, a handful of warnings popped up for a few unusable time zones, but the other 568 time zones imported just fine (yes, who knew there were 568 time zones?).

And that’s all it takes to use UTC in MySQL. But do note that you’ll occasionally need to update the time zone tables, as the particulars of time zones do change, albeit rarely.