Using time zones in a PHP web application

Question

I've been looking around for a few hours now about what's the best way to use timezones in a PHP/MySQL web application, finding a definitive answer is hard. From what I've learnt so far it is best to store everyones stuff in the database in UTC (correct me if I am wrong).

When a user registers I will ask them for there timezone and then store that against there user. This would be in this format as a dropdown menu:

<option value="Europe/London">(GMT) Greenwich Mean Time : London</option>

The app I am building will allow users to set arrangements in the future with people (meetings), much like a calendar. Obviously over the course of a year different timezones have different daylight savings periods, any idea how I would cater for this?

Say a user from the UK sets a meeting for 3:00PM on January 24th 2013 and invites someone who lives in California to this meeting, how do I get it so that the American sees that meeting in his/her timezone and the UK user sees it in his/her timezone? (Note that both users are signed up and have set their timezone).

Does anyone have a clear explanation and maybe some examples for this? Or can point me to where I can find that?

Thanks

Answer 1

I dealt with this situation extensively in a PHP/MySQL application I wrote for a private jet operator a little over a year ago. There are different strategies to handle timezones in these two platforms, but I will explain how I did it. I set the MySQL server to UTC and run each PHP script in the timezone that the user specifies during the signup process for the user profile.

MySQL and PHP (PHP 5.2 and above) both have native datetime datatypes. MySQL's datetime is a primitive data type, while PHP 5.2 and above offers the built-in DateTime class. The MySQL datetime datatype does not include metadata for the timezone, but a PHP DateTime object always includes a timezone. If the PHP datetime constructor does not specify the optional timezone in the second argument, then the PHP datetime constructor uses the php environment variable.

Both MySQL and PHP have default timezone set in the configuration files. MySQL uses the datetime set in the config file for each db connection unless the user specifies a different timezone after connection is started with the command SET time_zone = [timezone];. PHP also sets a timezone environment variable for each script using the timezone set in the server config file, and this environment variable can be overriden using the PHP function date_default_timezone_set() after the script starts.

The PHP DateTime class has a property called timezone, which is a PHP DateTimeZone object. The DateTimeZone object is specified using a string for the exact time zone. The list of timezones is comprehensive, having hundreds of individual time zones across the world. The PHP time zones will account for daylight savings time automatically.

When the user generates a datetime in the web app, construct a PHP datetime object in the timezone of the user's profile. Then use the setTimezone method to modify the DateTime object to the UTC timezone. Now you have the user's datetime in UTC, and you can store the value in the database. Use the DateTime format method to express the data as a string in the format accepted by MySQL.

So the user generates a datetime, and you make a PHP datetime object in the user's specified timezone:

// set using an include file for user profile
$user_timezone = new DateTimeZone('America/New_York'); 

// 1st arg in format accepted by PHP strtotime
$date_object1 = new DateTime('8/9/2012 5:19 PM', $user_timezone); 
$date_object1->setTimezone(new DateTimeZone('UTC'));
$formated_string = $date_object1->format('Y-m-d H:i:s');
$query_string = "INSERT INTO `t_table1` (`datetime1`) VALUES('$formated_string')";

When you retrieve the value from the database, construct in UTC and then convert to the user's time zone.

$query_string = "SELECT `datetime1` FROM `t_table1`";
$date_object1 = new DateTime($datetime_string_from_mysql, new DateTimeZone('UTC'));
$date_object1->setTimezone($user_timezone);
$string_for_display_in_application = $date_object1->format('m/d/Y g:i a');

Using this method, your datetime values are always stored in UTC inside the db, and the user always experiences the values in his/her profile's time zone. PHP will correct for Daylight Savings Time if necessary for each time zone.

One gotcha: This explanation does not cover the MySQL timestamp datatype. I recommend using the MySQL datetime datetype to store datetime values, not the timestamp datatype. The timestamp datatype is covered in the manual here.

Edit: You can produce an array containing every PHP timezone string using listIdentifiers, which is a static method of the DateTimeZone class.

Answer 2

In MySQL, what you need to do is:

1. Store each user's chosen timezone someplace you can retrieve it when you're doing database queries on behalf of that user. You can store it as a string.
2. Right before you do work on behalf of a particular user (for example, storing or retrieving appointment times and dates) do SET time_zone = (stored time zone setting)

This will cause time zones to be converted appropriately to each person's local time.

Edit:

This works because

1. MySQL tries to use UTC (universal time, formerly known as Greenwich Mean Time) to store DATETIME and TIMESTAMP data items in tables.
2. It can only do this correctly if it knows the correct local time zone for each data item it is given by applications.
3. In applications that don't care about different time zones, it does this in a MySQL-server-wide way. See https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html . Most people who run multi-national and multi-time-zone applications set their server time zones to UTC, not to local time, because it makes it much easier to keep things sorted out.
4. It makes very little sense to try to convert a time from UTC to local time unless you also know the date and the timezone, because local time switches on and off at various times of year. Just try to get this right for all three of Israel, Arizona, and New York, I dare you! Israel switches between daylight and standard time on Passover and Rosh Hashanah; Arizona doesn't switch, and New York switches at the whim of the US federal legislature.
5. There's a session-scope time_zone setting (SET time_zone = something). If you don't set it, it uses the timezone representation in item 3 above. If you set it, the server will use this as the timezone to convert its internal representation to the representation it sends back in queries.
6. You can get a list of the names of the available time zones in your MySQL server by issuing SELECT Name from mysql.time_zone_name. This can populate the pulldown menu from which your user can select her time zone. If this query returns no items, take a look at bottom of https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html .

So, that means you can set your session time_zone setting to a particular user's time zone, and then get back all times in that user's time zone. Also, any DATETIME or TIMESTAMP items you INSERT or UPDATE will be converted from that user's time zone to MySQL's internal representation as they are placed in your tables.

Be careful: in web applications with persistent MySQL connections, the work for a new user request will inherit the connection's time_zone setting. Don't forget to reset it for the new user.

If you're running a query returning local time data for more than one user, and those users happen to be in different time zones, you can't take advantage of this MySQL per-session feature set. You can work around this by running different queries for different users and changing the time_zone setting between them.

Or, you can use the MySQL function

CONVERT_TZ(datetime,'UTC','user_time_zone')

or similar on each item.

Alternatively, Java and DotNET have their own high-quality time zone manipulation systems. So, you can make the choice of running your MySQL server with the time_zone setting of UTC, and do all your timezone conversions in your application.