Handling User Time Zones

I've been working on a project where I need to record the dates and times when users perform certain actions. This wouldn't be so difficult if it weren't for the fact that the users themselves need to be able to see these times, and ideally, relative to their local time zones. For my purposes, the server time is adequate for keeping track, but it is both inconvenient to users and unfair to expect them to calculate their local time based on the server's. I'm sure most people on the Internet eventually become used to the fact that lots of sites and servers are set to not only US time, but EST/EDT in particular, but I'd like to offer the option to choose their own time zones.

When I first realized I wanted to implement this, I didn't think it entirely through, and assumed that it would only involve

1. adding a drop-down box to the sign-up/settings form,
2. some kind of easy identifier, e.g. an integer indicating the difference from GMT, as a field to the user information table, and
   
3. some extra code to look up time zones when displaying information.

Manageable, right?

Before I even make the drop-down box, I have to decide which time zones I'll work with. First, since I'm using PHP/MySQL, let's have a look at PHP's supported time zones. Far too many to throw at the user and the names aren't very intuitive, but they do have the benefit of PHP's inbuilt DateTime functions. The list Windows uses in its date and time settings is much shorter, and its more descriptive names can be mapped one to one with PHP's. So I can use the DateTime functions after all, though now I have to store the mapping somewhere.

Storing the mappings in an array in PHP would mean I lose the power and point of using databases but if I store it in the database, that means I'll have to query it just to display a sign-up form. Which I happen to have on the default index page, when users aren't logged in, so it will be shown every time somebody visits the homepage, more than the usual sign-up page. And because I want to be a little fancy, I intend on displaying the current local time along with each time zone name in the drop-down box. This takes away the option of caching the index page, and will force, at the least, the PHP drop-down box creation each time.

If I do decide on storing it in MySQL, it would probably make the most sense to have a lookup table with the PHP format, the Windows name that the users will see, and a unique ID I can just slot into the user information table.

But before I can confirm that I want to go this route, I have to decide if I really need to use PHP's DateTime functions. If I use just a simple number to show the difference from GMT along with the Windows name and store that in the user information table, I can avoid so much of the hassle, EXCEPT that I'll have to deal with DST myself, which is a hassle all on its own. Every time there's a change in the timezones, I'll need to update the calculating functions. PHP's DateTime will let me avoid much of that, though I will still need to sort the drop-down box display, of course, with varying DSTs moving some places in front of others depending on the season.

It's a tossup whether it's better to use a PHP array to store the time zones or MySQL, for they both have their benefits and disadvantages. With PHP, there is a loss in consistency with some fields still being stored in MySQL, increasing the possibility of changes to either causing problems later. It also weakens the power available from a full database for any manipulations, which, unlikely as seems now, could be an issue later.

When I store the actual date and time of a transaction, I will use the server time. The options when displaying to the user are either to convert from server time to user local time for each record or to store both server AND local times. The tradeoff is between speed and time, and I think in this case, I will sacrifice the space.

As I've describe each issue in this article, I've making one option more appealing, not because it is the best choice in every circumstance but because I think it suits the situation in my case and is what I would do.

So, I will store the 75 Windows time zones and their PHP mappings in my database and query each time for the sign-up form. Hopefully, the writing of this has clarified my plan in implementing this and I will be able to proceed with actually getting it to work.

Edited June 2, 2010:
Implementation for above.

1. timezones.txt is a mapping of the 75 "common" Windows time zones to their PHP DateTime objects. Note that each pair is separated by a pipe ("|"), "&amp" is used instead of "&" and that there is a blank line at the end. The second is a matter of choice, but the first and last are needed for the script to work.


2. To create the table:
   mysql_query("CREATE TABLE IF NOT EXISTS `timezones` (
`tzid` INT NOT NULL AUTO_INCREMENT ,
`phptz` VARCHAR(30) NOT NULL ,
`timezone` VARCHAR(60) NOT NULL ,
PRIMARY KEY (`tzid`))");
phptz is the PHP name, timezone is the Windows name, and tzid is simply the ID for each mapping. tzid is what you should use for related tables and as the shared attribute for joins for looking up time zones.
   


3. PHP code to populate the above table is given below. Note that the timezones.txt file needs to be in the same directory as this code, though of course that can be altered.
   $timezonefile = file_get_contents("timezones.txt");
preg_match_all("/(.*?)\|(.*?)\s\\n/", $timezonefile, $timezones);
$count = count($timezones[0]);
for ($i = 0; $i < $count; $i++) { mysql_query( "INSERT INTO timezones (phptz, timezone) VALUES (\"{$timezones[1][$i]}\", \"{$timezones[2][$i]}\")"); }


4. The function to generate the <select> code is as follows. Its usage is fairly straightforward, with a typical call being displaytimezones("Select your local time zone", "timezone"). There is also a parameter to choose the selected time zone which, if not given, is assumed to be America's Eastern time. The option values correspond to the tzid fields from the timezones database table.
   function displaytimezones($label, $name, $selected = "Eastern Time \(US & Canada\)") {
global $database;
$timezones = $database->query("SELECT * FROM timezones");
while ($tzrow = mysql_fetch_array($timezones)) {
$ndtz = new DateTimeZone($tzrow['phptz']);
$currentDate = new DateTime("now", $ndtz);
$displaytzid[] = $tzrow['tzid'];
$displaytznow[] = $currentDate->format("j M Y (g:ia)");
$displaytzname[] = $tzrow['timezone'];
$displaytzoffset[] = -1*($ndtz->getOffset($currentDate));
}
array_multisort($displaytzoffset, $displaytzname, $displaytzid, $displaytznow);
$count = count($displaytzoffset);
$display = " $label\n";
$display = $display." <select name="\" id="\">\n";
for ($i = 0; $i < $count; $i++) { $display = $display." {$displaytznow[$i]} - {$displaytzname[$i]}\n";
}
$display = $display." </select>\n
";
$display = preg_replace("/(.*?) - ($selected)/", "$2 - $3", $display);
echo $display;
}

A sample of the drop-down box can be seen here.