Working with Dates and Times - MySQL - page 188

zone different from the server and it inserts TIMESTAMP values without making the
proper time zone correction, the UTC values won't be correct.
Suppose that the server and client C1 are in the same time zone, and client C1 issues
these statements:
mysql> CREATE TABLE t (ts TIMESTAMP);
mysql> INSERT INTO t (ts) VALUES('2014-06-01 12:30:00');
mysql> SELECT ts FROM t;
+---------------------+
| ts |
+---------------------+
| 2014-06-01 12:30:00 |
+---------------------+
Here, client C1 sees the same value that it stored. A different client, C2, will also see the
same value if it retrieves it, but if client C2 is in a different time zone, that value isn't
correct for its zone. Conversely, if client C2 stores a value, that value when returned by
client C1 won't be correct for the client C1 time zone.
To deal with this problem so that TIMESTAMP conversions use the proper time zone, a
client should set its time zone explicitly by setting 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 initially is assigned that same value as its session time zone:
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +06:00 | +06:00 |
+--------------------+---------------------+
When client C2 connects, it sees the same TIMESTAMP value as client C1:
mysql> SELECT ts FROM t;
+---------------------+
| ts |
+---------------------+
| 2014-06-01 12:30:00 |
+---------------------+
But that value is incorrect if client C2 is only four hours ahead of UTC. C2 should set
its time zone after connecting so that retrieved TIMESTAMP values are properly adjusted
for its own session:
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 |
+--------------------+---------------------+