Changing the MySQL Timezone

Wednesday 11 Apr 2007 17:52

Changing the MySQL Timezone

Find the MySQL config file 'my.cnf', add the following section (if not already present) and set your timezone.

[mysqld_safe]
timezone = Europe/London

I'm a big fan of retrieving DATETIME fields from MySQL databases using the UNIX_TIMESTAMP function. This returns dates in a format I can do stuff with easily using PHP's Date/Time functions.

SELECT UNIX_TIMESTAMP(my_date) my_date FROM my_table

A UNIX_TIMESTAMP is seconds since 1 Jan 1970 00:00 GMT (or UTC which is the same). This handy format can be used with PHP functions such as date(). Plus, as a number its easy to add or subtract times by simply adding the number of seconds.

$d = $row->my_date;            // unix timestamp datetime from database
$d += 60*60*24;                // add 1 day (60 secs * 60 mins * 24 hours)
echo date("Y-m-d H:i", $date); // print the date in format YYYY-MM-DD HH:MM

But you can encounter problems when using functions such as date with values you've retrieved from your MySQL tables as these functions convert to the current MySQL timezone. In my case this problem came to light when a server patch somehow altered this setting and the dates on my blog went 5 hours out. This was made even worse by the fact that I feed those dates back into queries on my database. The answer, as dude and myself found out is to set the MySQL timezone.

Related articles

Comments(3)

captcha
26 Apr 2011 03:49 by Christian vdB
Europe/London is UTC in winter, but UTC+1 in summer.
1 Apr 2010 02:31 by kirk
A better example, of course - is to set your blog up so that it works with a timezone that you set with your application, rather than the server - as these sorts of settings are generally application/user specific.
7 Mar 2008 07:34 by hair balls
really hairy