Skip to content

time_zone time zone support

The time zone used by MatrixOne depends on three system variables: global.time_zone, session.time_zone and global.system_time_zone.

  • global.system_time_zone represents the server system time zone. When the server starts, it tries to determine the host's time zone and uses it to set the system time zone (system_time_zone).

  • global.time_zone represents the server's current time zone. The initial time_zone value is SYSTEM, indicating that the server time zone is the same as the system time zone.

You can use the following statement to set the global server time zone at runtime. It will not take effect in the current session after the settings are completed. You need to exit the current session first and reconnect MatrixOne again to take effect.

> SET GLOBAL time_zone = timezone;
  • Each session has its own session time zone, which is determined by the time_zone variable of the session at that time. Initially, the variable value of the session time zone is obtained from the global time_zone variable, but the session client can change its own time zone. However, this setting will only be valid during the current session.
SET time_zone = timezone;

Use the following SQL statement to view the values ​​of the current global time zone, client time zone, and system time zone:

> SELECT @@global.time_zone, @@session.time_zone, @@global.system_time_zone;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| @@time_zone | @@time_zone | @@system_time_zone |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| timezone | +08:00 | CST |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

Set the format of the value of time_zone:

  • The value SYSTEM indicates that the time zone should be the same as the server system time zone.

  • The value UTC means that the time zone is set to UTC (Coordinated Universal Time). Only the "UTC" abbreviation is supported as a time zone.

  • This value can be given as a string, representing the offset of UTC time, in the format "HH:MM", with + or -, such as +10:00 or -6:00. The allowed range is "-13:59" to "+14:00".

The current session time zone setting affects the display and storage of time zone-sensitive time values. That is, it affects the values ​​queried by executing functions such as NOW() and the values ​​stored in the TIMESTAMP column and queryed from the TIMESTAMP column.

The session time zone setting does not affect the values ​​displayed by functions such as UTC_TIMESTAMP() or values ​​in columns DATE, TIME or DATETIME columns.

Note

Only the value of the Timestamp data type is affected by the time zone. It can be understood that the actual representation of the Timestamp data type uses (literal + time zone information). Other time and date types, such as Datetime/Date/Time, do not contain time zone information, so they are not affected by time zone changes.

> SELECT @@global.time_zone, @@session.time_zone, @@global.system_time_zone;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| @@time_zone | @@time_zone | @@system_time_zone |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SYSTEM | SYSTEM | CST |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

> 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.02 sec)

mysql> set @@time_zone = '+08: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 the above example, no matter how the time zone value is adjusted, the value of the Datetime type field is unaffected, while Timestamp displays changes as the time zone changes. In fact, the value persisted to the stored timestamp has never changed, but the display value is different depending on the time zone.

Note

Values ​​of the Timestamp type and Datetime type, the time zone will be involved in the conversion process of each other. This situation is handled based on the current time_zone time zone.

Modify the MatrixOne time zone

  1. Check the current time or time zone:
> select now();
+----------------------------------+
| now() |
+----------------------------------+
| 2022-10-14 18:38:27.876181 |
+----------------------------------+
1 row in set (0.00 sec)

> show variables like "%time_zone%";
+---------------------------+
| Variable_name | Value |
+---------------------------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+---------------------------+
2 rows in set (0.00 sec)

  • time_zone: The time zone using system.

  • system_time_zone Description system Uses the server system time zone.

  • Modify the current time zone:

set global time_zone = '+08:00';
set time_zone = '+08:00';
  • set global time_zone = '+08:00';: Modify the global time zone of MatrixOne to Beijing time, that is, the East 8th District where we are located.
  • set time_zone = '+08:00';: Modify the current session time zone.

limit

MatrixOne only supports the (+/-)HH:MM format and UTC to set the value of time_zone.