understanding results of global.time_zone

Question

Hi

I'm looking into ways of changing the time zone of my databases as my userbase is UK based, but my domain is US based. I've researched a number of options including php, PEAR time class, and MySQL functions. To me the simplest (largly because it starts and ends with MySQL) involves changing MySQL's time_zone parameter.

In preparation to do this I've just tried MySQL's @@global.time_zone call (like I'd better understand this before I start playing with it). It's produced a whole load of parameters that I didn't expect, and MySQL's site (link text) fails to mention - it's obviously an array, it returns lots of recognisable parameters like log-in protocols etc but I can't see any obvious parameters that return anything to do with timezones.

Do you have any experience of this? Can you advise? Many thanks in advance.

Answer 1

The standard practice is to store any date/times you want to display in UTC (i.e. GMT) and either automatically fetch the timezone from the user's client using javascript, or asking the user where he lives.

Personally, I don't endorse the automatic method, since many users don't have the correct timezone set. But it can be useful to suggest a default to them.

Either way, once you have the user's timezone, just add it to the timestamps stored in UTC in the database before you display it.

Answer 2

There are 2 keys to dealing with timezones:

1. Store all of your date/times in the same timezone (can be any timezone, but using UTC does simplify things.)

2. Decide where in your application date/times will be converted to/from the storage timezone to the user display timezone. This can be done in the database abstraction layer, the application code, or in the page templates (any part of the MVC stack basically). The important thing is to be consistent.

Also remember that php has its own timezone setting independent of MySQL. If you're running php >5.1, you can change the php timezone at runtime using date_set_default_timezone().

If you decide to do the timezone conversion when you retrieve/store information in the database, MySQL's convert_tz() function will become your new best friend.

For me it's has been easiest to do the timezone conversions as part of the page templates. Date/times get stored in the database in the UTC timezone, and all application logic is in UTC as well. When outputting a date in HTML, I use a wrapper function for php's date() to output the date in the user's timezone:

function getTimezoneOffset($time, $timezone)
{    
    $t = new DateTime(date('Y-m-d H:i:s', $time));  
    $tz = new DateTimeZone($timezone);  
    $t->setTimeZone($tz);   
    return $t->getOffset();
}
function myDate($timezone, $format, $timestamp=false)
{
    if (!$timestamp) $timestamp = time();
    return date($format, $timestamp+getTimezoneOffset($timestamp, $timezone));
}

Likewise, when taking input from the user via forms, do the timezone conversion first so that your application has UTC date/times to work with.

Another option is to use PostgreSQL -- unlike MySQL it has timezone support for datetimes built-in.