Timezone differences between PHP and MySQL

Question

I've run in to an issue whereby PHP and MySQL seem to disagree on which timezone they should be using when converting certain timestamps back to date and time.

The problem arises when I have a timestamp which I would like to convert back to a datetime format. PHP gives the date and time based on GMT being the timezone, but MySQL seems to think it is operating in GMT+1, and converts accordingly.

Simple reproduction

$original_date = '2009-08-31 23:59:59';
$timestamp = strtotime($original_date);
echo $timestamp;                                    // Gives 1251763199

echo date('Y-m-d H:i:s', $timestamp);               // PHP:   2009-08-31 23:59:59
echo db_field('SELECT FROM_UNIXTIME(1251763199)');  // MySQL: 2009-09-01 00:59:59

The timestamp given by PHP seems to be the correct one for the date given, assuming timezone is GMT. The result from MySQL would have been the correct one had we been running BST (timestamp given fell within GMT+1 at that time).

If I try the above with a $timestamp from today (1267640942), PHP and MySQL both seem to be happy to tell me that it is 2010-03-03 18:29:02 (both returning GMT).

What timezone is set on the servers?

I've checked the MySQL docs, which say that if my timezone is set to system than the OS will provide the timezone info. This appears to be the case at the moment:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              | 
+--------------------+---------------------+

The default timezone on my web server is GMT. This is also the default system timezone on my database server (according to the date command run on the cl on each server).

foo@bar:/home/foo$ date
Wed Mar 3 18:45:02 GMT 2010

So according to the docs, my DB server should be running on GMT, which is what we want. Yet the query output given in my test scripts suggests that it's running in GMT+1.

I know there are a number of workarounds for this problem (all date arithmetic being done fully in either PHP or MySQL, etc) but I'd love to get to the bottom of what's causing this discrepancy so we can sort it out and prevent anyone else on the team from being tripped up by it.

Does anyone know if there's a very basic setting that I've over-looked here, or know what could be causing this discrepancy?

Thanks!

Answer 1

I would suggest using UTC on your server and MySQL install and convert the timezone(s) to what ever you want. The conversion in MySQL and PHP are fairly simple.

• http://www.w3schools.com/php/php_ref_date.asp (getTimezone and setTimezone)
• http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Answer 2

i use this methodology:

take care of PHP and leave mysql alone

it is recommended to set default timezone for php via date_default_timezone_set php function.

use a TIMESTAMP field type for keeping date record in mySql

then when you insert:

$sDate = date("Y-m-d H:i:s");
mysql_query("insert into `table` set `created_on` = '$sDate' ");

and when you select:

mysql_query("select `created_on` from `table` ");
$iTime = strtotime($aRow['created_on']);

you can always have access to global time using gmdate php function:

$iTime_Global = gmdate("U", $iTime);

the mysql timezone would have no effect in your application if you just take care of your PHP code. (that is made by timezone set)