Suppose you have a PHP/MySQL application that you want to adapt to work across multiple timezones. I.e. Users from Toronto should see all dates and times in Eastern time while Vancouver users should see times in Pacific time. On this surface this appears like a simple thing to do, but there are a number of pitfalls to watch out for.
For this design pattern we’ll assume that we can always obtain the user’s timezone by calling a function, say getUserTimezone() which would return something like Canada/Pacific.
The tricky part is making sure that different users can view and edit dates in their local timezone, while keeping the data consistent. For example, if Toronto Tony posts an event in our application, he can specify that it starts at 8pm (meaning 8pm Toronto time). When Vancouver Vinnie logs in, he sees this event, but the time says ‘5pm’ because he sees the event in Vancouver time.
Design Decisions
- How do we store the dates in the database? Do we store them in Toronto time? Vancouver time? Some other time? or does it matter?
- Both MySQL and PHP contain functions to convert dates between different timezones. Do we do the conversion in MySQL? In PHP? In both? Does it matter?
Our solution for the first design decision, is that it doesn’t matter how we store the dates in the database, as long as we are consistent. I.e. we must store ALL of the dates in Toronto time or ALL of the dates in Vancouver time, but we cannot store some in one timezone and some in another. When deciding on an appropriate default timezone for the database, I considered using GMT (Greenwich mean time) but it is probably better just to leave the server running the most intuitive time for the majority of users. If the server is physically located in Denver, why not just leave the times in Denver time. We will convert the times to and from the users’ timezones when we insert and retrieve the data from the database.
For the 2nd design decision,, it will be much easier to use the MySQL conversion functions than to use the PHP functions. This will allow us to simplify our handling of the timezone issue exclusively to our SQL queries and leave our PHP code largely alone.
Setting the Timezone in PHP
Inside our PHP script we can set the timezone for the current user by setting the TZ environment variable:
putenv('TZ='.getUserTimezone());
Once we have set the timezone, the PHP date function will properly convert timestamps to the user’s local timezone, and the strtotime function will propertly convert dates in the user’s local timezone back to timestamps.
Now we have 3 choices in how to store our dates in the database and interact with them.
Strategy 1: Store dates as timestamps
Since timestamps are not affected by timezones, we could just store all of our dates in INT columns as timestamps and convert them to dates using PHP. e.g.:
// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., '".strtotime($date)."', ...)");
// retrieving dates
$res = mysql_query("select `startDate` from events where ...");
list($date) = mysql_fetch_row($res);
$date = date('Y-m-d H:i:s', $date); // convert the timestamp back to a date string
The main drawback to this strategy is style. Databases are equipped with DATE data types for a reason. By using INT datatypes to store our dates, we are missing out on some nice features for data manipulation etc… Plus it just doesn’t *feel* quite right to be using INT columns when we should be using DATETIME columns.
Strategy 2: Use FROM_UNIXTIME() and UNIX_TIMESTAMP() mysql functions
MySQL provides a some nice conversion functions that will allow us to work with timestamps yet still store our dates in proper DATE and DATETIME columns in the database. The FROM_UNIXTIME() function will convert a timestamp into a date string, and the UNIX_TIMESTAMP() function will convert a date string into a unix timestamp. So we can achieve the same as in Strategy 1 with the following:
// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., FROM_UNIXTIME('".strtotime($date)."'), ...)");
// retrieving dates
$res = mysql_query("select UNIX_TIMESTAMP(`startDate`) from events where ...");
list($date) = mysql_fetch_row($res);
$date = date('Y-m-d H:i:s', $date); // convert the timestamp back to a date string
This strategy is a little bit better, in my opinion, because it uses the proper data types for the proper data. However it still requires that we use timestamps as an intermediary. In certain cases you may be unable to use timestamps (either because the rest of the system expects the SQL queries to return and accept date strings directly (not timestamps)), or because timestamps won’t handle the extreme dates in your application (e.g. timestamps are only valid for dates later than 1901 on most systems, and later that 1970 on some).
Strategy 3: Use the MySQL CONVERT_TZ() function
MySQL’s CONVERT_TZ() function will convert a date between two different timezones without requiring the intermediate step of converting to a timestamp. Theoretically, it should be able to accept input of the form:
CONVERT_TZ(`startDate`, 'Canada/Pacific', 'Canada/Eastern');
However if your server doesn’t have the timezone tables installed, then it won’t work correctly with these forms of timezones. The safest way seems to be to specify timezones in the form ‘+08:00’, meaning (8 hours behind GMT). e.g.
CONVERT_TZ(`startDate`, '+01:00', '-08:00');
This creates a small hurdle for us: How do we convert the user’s timezone (in the form ‘Canada/Pacific’) to an offset timezone like ‘+08:00’?
Here is a function that does just that:
/**
* Returns the current timezone as an offset (e.g. +08:00) of GMT
**/
utc_offset(){
// Find the difference in seconds between GMT and local time.
$diff = gmmktime(0,0,0,12,29,2007) - mktime(0,0,0,12,29,2007);
$sign = ( ( $diff >= 0 ) ? '+' : '-');
$diff = abs($diff);
$hours = str_pad(strval(floor($diff/3600)), 2, '0',STR_PAD_LEFT);
$minutes = str_pad(strval(floor($diff/60) % 60), 2, '0',STR_PAD_LEFT);
return $sign.$hours.':'.$minutes;
}
So we can use our utc_offset() function to get the user’s timezone as an offset from GMT. We can pass this as one of the parameters for CONVERT_TZ(). The other timezone will be the system timezone, which we can conveniently specify as ‘SYSTEM’. e.g.:
-- Convert from -08:00 to system time
CONVERT_TZ(`startDate`, '-08:00', 'SYSTEM')
-- Convert from system time back to -08:00 time.
CONVERT_TZ(`startDate`, 'SYSTEM','-08:00')
We now have all the tools we need to adapt our examples from strategies 1 and 2:
// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., CONVERT_TZ('$date','".utc_offset()."','SYSTEM'), ...)");
// retrieving dates
$res = mysql_query("select CONVERT_TZ(`startDate`,'SYSTEM','".utc_offset()."') as `startDate` from events where ...");
list($date) = mysql_fetch_row($res);
echo $date; // 2007-12-29 08:00:00
I used this strategy to add timezone support to Xataface because I needed a solution that would allow me to change the mysql queries without altering any of the code them uses them. It seems to work quite well.
Happy hunting!