building timezone feature in PHP web application

Question

This is almost similar question to this one: -

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

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.

Answer 2

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)