How to correctly set mysql timezone

Question

I have a weird problem concerning mysql timezone.

In my website config file i have this line which sets the timezone :

mysql_query("SET SESSION time_zone = '$offset';"); // Offset is properly calculated, no worries about that

The funny part is that if i add another line right after this like this :

$q = mysql_query("SELECT NOW() as now");
$row = mysql_fetch_array($row);
echo $row["now"];

After executing that code, the time is displayed correctly.

BUT, in some other queries i insert rows in tables that have a column named date that defaults to CURRENT_TIMESTAMP.

Rows are inserted like this:

INSERT INTO `sessions` (`user_id`) VALUES `1`

(The sessions table has a date column that defaults to CURRENT_TIMESTAMP)

But the value inserted in DB still points back to the timezone of the server :((

Any ideas how to work through this ?

Answer 1

You have to understand that MySQL maintains multiple time zone settings:

• System time zone (basically the time zone set in OS)
• Server time zone (the time zone used by MySQL)
• Client time zone (the session time zone used per connection)

See http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html for details.

Date/time values are stored in two different ways:

• All unix timestamp based values are always stored in UTC. They are internally converted from and to Client time zone on the fly when they are stored and read. The same is true for NOW() and CURTIME() functions as they are timestamp based.
• DATE, TIME and DATETIME columns (which store their values in a year-month-day hour-minute-second format) are NOT affected by time zone settings and are never converted.

From the above it should become clear that the values that you see when you read from unix timestamp based columns are not necessarily what is really stored in the DB. They are converted using the server time zone and the client time zone. The result can be confusing if you do not understand the details of the mechanics.

For a first test try to find out the current settings in each of your client programs by executing

SELECT @@global.time_zone, @@session.time_zone;

The global time zone will always be the same. But the session time zone can differ from client application to client application and will change the results of your read and write operations.