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.
Have the client specify its time zone when it connects to the
server by setting the
time_zone
system
variable.
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 (Determining the Current Date or Time).
To convert individual date-and-time values from one time zone to
another, use the
CONVERT_TZ()
function (Shifting a Date-and-Time Value to a Different Time Zone).
3.14.141.115