building timezone feature in PHP web application

Question

This is almost similar question to this one: -

http://stackoverflow.com/questions/346770/dealing-with-timezones-in-php

I read that thread, and have some more issues, and not sure of how to elegantly build timezone support in PHP/MySQL application.

I am writing an application in PHP, where users can select their timezone.

Here, all timestamps are stored in GMT in MySQL. And I am converting the timezone back to users timezone, when presenting the data.

Now these are the problems I am facing: -

1) I show the user, a list of timezones populated from MySQL's timezone tables, it is a HUGE list and is difficult to chose from.

So, I want to implement something like windows time zone list. Offset with region info.

2) The PHP and MySQL may have different set of list.

3) Where should I convert timezones, in PHP or in MySQL? I have combination of coding. Sometimes it is easy to select a column converted from MySQL, sometimes it is easy to convert in PHP. So, here the previous point becomes very important. For example, in MySQL's time_zone tables Asia/Kolkata, and Asia/Calcutta both were there, but in PHP 5.2.6's default timezonedb, only Asia/Calcutta is present.

Answer 1

1. Choosing a time zone from a large list isn't that difficult. Sort the list by standard-time offset so that users can quickly scroll to the correct offset, and from there, look for their exact location.

2. Choose one list. I would tend to go for whichever list is shorter. Only present those choices to the user.

3. Do the math where the list that was chosen is. If you're presenting the MySQL list, have MySQL do the date math, if you're using PHP's list, have PHP do the math. This will make so that you don't need to figure out some weird mapping for missing zones in one implementation.

A fancy solution could involve asking the user's location and extrapolating their timezone. (However, have the option to explicitly set it as well)

Answer 2

This is the sort of situation where a custom class might be the best solution. Then you can move times and dates around with their timezones. Luckily, PHP v5 has just such an object built-in. You just have to remember to code things so that when you rely on an implicit timezone, it's because you know you've earlier set it explicitly. This also means if you don't know what the implicit value is, you go ask, effectively making it explicit.

Looking at PHP's timezone support and MySQL's timezone support, I would do timezone stuff largely in PHP. MySQL's support is largely oriented around it's concept of "now".

Oh yes: you do not want to do explicit date arithmetic (e.g. adding 86400 seconds to add "one day"). Let the PHP object or libraries do that. They will have bugs fixed you will not have thought of. (I once wrote an object for a specialized type of date manipulation and spent weeks battling an intermittent off-by-one-hour bug, until I discovered the one spot that was adding 86400 to make a day transition instead of using mktime(). It turned out that daylight-saving rules were messing things up. Which, of course, I wasn't checking for.)

Answer 3

When faced with this exact issue, I found this reference, which maps the succinct, Windows-style timezone list to a subset of the ridiculously exhaustive Unix-style timezone list.

Users are presented with a dropdown of these windows-style names (e.g., (GMT-05:00) Eastern Time (US & Canada)), and their selection is stored in the db in the unix-style format (e.g. America/New_York)

The work of applying the user's timezone preference is done in PHP at display time, using the DateTime class. I think I'd recommend this, so you can be confident that the dates you're manipulating in SQL/PHP are always in UTC, until they're displayed.