Home / General Questions / Question No: 115

MySQL Server allows to set the current time zone on a per-connection basis. Time zone settings are determined by the time_zone system variable. To view current Time Zone run the following query:

SHOW VARIABLES LIKE "time_zone";

Time zone settings are determined by the time_zone system variable. The server maintains a global time_zone value, as well as a session time_zone value for each client that connects to . The session value is initialized for a given client, from the current value of the global time_zone variable, when the client connects.

The default setting for the global value is SYSTEM, which thus also becomes each client's initial session time_zone value. The global and session time zone settings can be retrieved with the following statement:

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

MySQL Server stores TIMESTAMP values internally in UTC. It converts TIMESTAMP values from the server's current time zone for storage, and converts back to the current time zone for retrieval. The standard setting for both the server and the per-client connection is to use the SYSTEM setting, which the server retrieves from the host at startup.

If the time zone setting is the same for both storage and retrieval, you will get back the same value you store. If you store a TIMESTAMP value, and then change the time zone to a different value, the returned TIMESTAMP value will be different from the one you stored.

The following examples demonstrate how to change the session time zone settings to store and retrieve TIMESTAMP data. First, we set the session time zone to UTC, that is, '+00:00':

mysql> SET time_zone = '+00:00';

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

Next, we create a simple table containing just a TIMESTAMP column named ts and insert one record that assigns the current time to ts. Then we retrieve the record:

mysql> CREATE TABLE ts_test (ts TIMESTAMP);

mysql> INSERT INTO ts_test (ts) VALUES (NULL);

mysql> SELECT * FROM ts_test; 
+---------------------+ 
| ts | 
+---------------------+ 
| 2005-01-04 20:50:18 | 
+---------------------+ 

Finally, we change the session time zone twice, each time retrieving the value after the change. This demonstrates that, even though we're retrieving the same TIMESTAMP value, the change in time zone setting causes the "localized" display value to be different each time:

mysql> SET time_zone = '+02:00';

mysql> SELECT * FROM ts_test; 
+---------------------+ 
| ts | 
+---------------------+ 
| 2005-01-04 22:50:18 | 
+---------------------+ 
mysql> SET time_zone = '-05:00';

mysql> SELECT * FROM ts_test; 
+---------------------+ 
| ts | +---------------------+ 
| 2005-01-04 15:50:18 | 
+---------------------+

The per-connection time zone settings also influence other aspects of the MySQL server that depend on the current time, most notably the function NOW().

MySQL Server also supports the CONVERT_TZ() function, which performs time zone conversions of datetime values:

mysql> SELECT CONVERT_TZ('2005-01-27 13:30:00', '+01:00', '+03:00');
+-------------------------------------------------------+ 
| CONVERT_TZ('2005-01-27 13:30:00', '+01:00', '+03:00') | 
+-------------------------------------------------------+ 
| 2005-01-27 15:30:00 | 
+-------------------------------------------------------+

CONVERT_TZ() assumes that the given datetime value has the time zone represented by the first hour/minute offset argument, and converts it to a value in the time zone represented by the second offset argument. The result is that you get the same datetime value, from the point of view of a different time zone.