Changing the MySQL Timezone

Tags: MySQL, timezone, PHP

[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.

Comments (1)