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
Advertisement
Answer
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.