Recipe 6.3. 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 ts FROM t; +---------------------+ | ts                  | +---------------------+ | 2006-06-01 12:30:00 | +---------------------+ 

Here, client A sees the same value that it stored. A different client B will also see the same value if it retrieves it, but if client B is in a different time zone, that value isn't correct for its zone. Conversely, if client B stores a value, that value when returned by client A won't be correct for the client A time zone.

To deal with this problem so that TIMESTAMP conversions happen for the correct time zone, a client can set its time zone explicitly. To specify the client time zone, set the session value of the time_zone system variable. Suppose that the server has a global time zone of six hours ahead of UTC. Each client is assigned that same value as its initial session time zone:

mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | +06:00             | +06:00              | +--------------------+---------------------+ 

Client B mentioned earlier will see the same TIMESTAMP value as client A when it connects:

mysql> SELECT ts FROM t; +---------------------+ | ts                  | +---------------------+ | 2006-06-01 12:30:00 | +---------------------+ 

If client B is only four hours ahead of UTC, it can set its time zone after connecting like this:

mysql> SET SESSION time_zone = '+04:00'; mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | +06:00             | +04:00              | +--------------------+---------------------+ 

Then when client B retrieves the TIMESTAMP value, it will be properly adjusted for its own time zone:

mysql> SELECT ts FROM t; +---------------------+ | ts                  | +---------------------+ | 2006-06-01 10:30:00 | +---------------------+ 

The client time zone also affects the values displayed from functions that return the current date and time (Section 6.4).

See Also

To convert individual date-and-time values from one time zone to another, use the CONVERT_TZ⁠(⁠ ⁠ ⁠) function (Section 6.12).




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net