MySQL Cookbook, 2nd Edition

Setting the Client Time Zone

Problem

You have a client that is in a different time zone from the server, so when it stores TIMESTAMP values, they don’t have the correct UTC values.

Solution

Have the client specify its time zone when it connects to the server by setting the time_zone system variable.

Discussion

MySQL interprets TIMESTAMP values with respect to each client’s time zone. When a client inserts a TIMESTAMP value, the server converts it from the time zone associated with the client connection to UTC and stores the UTC value. (Internally, the server stores a TIMESTAMP value as the number of seconds since 1970-01-01 00:00:00 UTC.) When the client retrieves a TIMESTAMP value, the server performs the reverse operation to convert the UTC value back to the client connection time zone.

The default connection time zone is the server’s time zone. The server examines its operating environment when it starts to determine this setting. (To use a different value, start the server with the --default-time-zone option.) If all clients are in the same time zone as the server, nothing special need be done for the proper TIMESTAMP time zone conversion to occur. But if a client is running in a time zone different from the server and inserts TIMESTAMP values, the UTC values won’t be correct.

Suppose that the server and client A are in the same time zone, and client A issues these statements:

mysql>CREATE TABLE t (ts TIMESTAMP);
mysql> INSERT INTO t (ts) VALUES('2006-06-01 12:30:00');
mysql> SELECT ...