Time Zone Support

The time zone in TiDB is decided by the global time_zone system variable and the session time_zone system variable. The default value of time_zone is SYSTEM. The actual time zone corresponding to System is configured when the TiDB cluster bootstrap is initialized. The detailed logic is as follows:

• Prioritize the use of the TZ environment variable.
• If the TZ environment variable fails, extract the time zone from the actual soft link address of /etc/localtime.
• If both of the above methods fail, use UTC as the system time zone.

You can use the following statement to set the global server time_zone value at runtime:

mysql> SET GLOBAL time_zone = timezone;

Each client has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

You can use the following statement to view the current values of the global and client-specific time zones:

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

To set the format of the value of the time_zone:

• The value 'SYSTEM' indicates that the time zone should be the same as the system time zone.
• The value can be given as a string indicating an offset from UTC, such as '+10:00' or '-6:00'.
• The value can be given as a named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'.

The current session time zone setting affects the display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(),

Note:

Only the values of the Timestamp data type is affected by time zone. This is because the Timestamp data type uses the literal value + time zone information. Other data types, such as Datetime/Date/Time, do not have time zone information, thus their values are not affected by the changes of time zone.

mysql> create table t (ts timestamp, dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> set @@time_zone = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values ('2017-09-30 11:11:11', '2017-09-30 11:11:11');
Query OK, 1 row affected (0.00 sec)

mysql> set @@time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+---------------------|---------------------+
| ts                  | dt                  |
+---------------------|---------------------+
| 2017-09-30 19:11:11 | 2017-09-30 11:11:11 |
+---------------------|---------------------+
1 row in set (0.00 sec)

In this example, no matter how you adjust the value of the time zone, the value of the Datetime data type is not affected. But the displayed value of the Timestamp data type changes if the time zone information changes. In fact, the value that is stored in the storage does not change, it's just displayed differently according to different time zone setting.

Note:

• Time zone is involved during the conversion of the value of Timestamp and Datetime, which is handled based on the current time_zone of the session.
• For data migration, you need to pay special attention to the time zone setting of the primary database and the secondary database.