Preface:

When using MySQL, you may encounter time zone related issues, such as time display error, time zone is not east 8, the program obtained time and database stored time, etc. In fact, these problems are related to the database time zone Settings, this article will start with the database parameters, gradually introduce the time zone related content.

1. Parameter description of log_timestamps

First, the log_timestamps parameter does not affect the time zone, but it does affect the time when certain logs are recorded. This parameter mainly controls the display time in error log, slow log and Genera log files, This does not affect the display times of general logs and slow logs written to tables (mysql.general_log, mysql.slow_log).

The log_timestamps parameter is a global parameter that can be dynamically modified. The default time zone is UTC. If this parameter is specified, the time recorded in the log is 8 hours later than the Beijing time. You can change it to SYSTEM to use the SYSTEM time zone. The following is a simple test of the function of this parameter and the modification method:

# check parameter values
mysql> show global variables like 'log_timestamps';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| log_timestamps | UTC | +----------------+-------+ 1 row in set (0.00 sec)  Generate slow logs mysql> select sleep(10),now(a);+-----------+---------------------+ | sleep(10) | now() | +-----------+---------------------+ | 0 | 2020-06-24 17:12:40 | +-----------+---------------------+ 1 row in set (10.00 sec)  Slow log file content discovery time is UTC time # Time: 2020-06-24 T09: ". 555348 z # User@Host: root[root] @ localhost [] Id: 10 # Query_time: 10.000354 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1592989960; select sleep(10),now(a); # Change parameter values to test again mysql> set global log_timestamps = SYSTEM; Query OK, 0 rows affected (0.00 sec)  mysql> select sleep(10),now(a);+-----------+---------------------+ | sleep(10) | now() | +-----------+---------------------+ | 0 | 2020-06-24 17:13:44 | +-----------+---------------------+ 1 row in set (10.00 sec)  Slow log files record content at the correct time T17: # Time: 2020-06-24 when. 514413 + 08:00 # User@Host: root[root] @ localhost [] Id: 10 # Query_time: 10.000214 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1592990024; select sleep(10),now(a);Copy the code

2. Time_zone parameter Description

The time_zone parameter is used to set the time zone of each connection session. This parameter is global and session level and can be changed dynamically. The default value is SYSTEM. In this case, the global system_time_zone parameter is used. The system_TIME_zone parameter inherits the time zone of the current SYSTEM by default.

Time zone Settings affect the display and storage of time zone sensitive time values. Include values displayed by some functions (such as now(), curtime()), and values stored in the TIMESTAMP type, but do not affect the values in the DATE, TIME, and DATETIME columns because these data types are accessed without TIME zone conversions, The TIMESTAMP type is actually the UTC time stored in the database. The query will display different times according to the specific time zone.

Let’s test the impact of the time_zone parameter change:

# Check the time zone of the Linux system
[root@centos ~]# date
Sun Jun 28 14:29:10 CST 2020

MySQL > select * from 'MySQL'
mysql> show global variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.00 sec)  mysql> select now(a);+---------------------+ | now() | +---------------------+ 14:31:12 | 2020-06-28 |+---------------------+ 1 row in set (0.00 sec)  Create test table, insert some data mysql> CREATE TABLE `time_zone_test` (  -> `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Increment primary key'. -> `dt_col` datetime DEFAULT NULL COMMENT 'a datetime time'. -> `ts_col` timestamp DEFAULT NULL COMMENT 'timestamp time'. -> PRIMARY KEY (`id`)  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time_zone test table '; Query OK, 0 rows affected, 1 warning (0.07 sec)  mysql> insert into time_zone_test (dt_col,ts_col) values ('the 2020-06-01 17:30:00'.'the 2020-06-01 17:30:00'), (now(),now()); Query OK, 2 rows affected (0.01sec)Records: 2 Duplicates: 0 Warnings: 0  mysql> select * from time_zone_test; +----+---------------------+---------------------+ | id | dt_col | ts_col | +----+---------------------+---------------------+ | 1 | 2020-06-01 17:30:00 | 2020-06-01 17:30:00 | | 2 | 2020-06-28 14:34:55 | 2020-06-28 14:34:55 | +----+---------------------+---------------------+  # change time zone to UTC and reconnect to find timestamp stored in time zone changes mysql> set global time_zone='+ 0:00'; Query OK, 0 rows affected (0.00 sec) mysql> set time_zone='+ 0:00'; Query OK, 0 rows affected (0.00 sec)  mysql> show global variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | +00:00 | +------------------+--------+ 2 rows in set (0.00 sec)  mysql> select now(a);+---------------------+ | now() | +---------------------+ 06:36:16 | 2020-06-28 |+---------------------+ 1 row in set (0.00 sec)  mysql> select * from time_zone_test; +----+---------------------+---------------------+ | id | dt_col | ts_col | +----+---------------------+---------------------+ | 1 | 2020-06-01 17:30:00 | 2020-06-01 09:30:00 | | 2 | 2020-06-28 14:34:55 | 2020-06-28 06:34:55 | +----+---------------------+---------------------+ 2 rows in set (0.00 sec)  Return to normal mysql> set global time_zone=From the '+'; Query OK, 0 rows affected (0.00 sec)  mysql> set time_zone=From the '+'; Query OK, 0 rows affected (0.00 sec)  mysql> show global variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | +08:00 | +------------------+--------+ 2 rows in set (0.00 sec)  mysql> select now(a);+---------------------+ | now() | +---------------------+ 14:39:14 | 2020-06-28 |+---------------------+ 1 row in set (0.00 sec)  mysql> select * from time_zone_test; +----+---------------------+---------------------+ | id | dt_col | ts_col | +----+---------------------+---------------------+ | 1 | 2020-06-01 17:30:00 | 2020-06-01 17:30:00 | | 2 | 2020-06-28 14:34:55 | 2020-06-28 14:34:55 | +----+---------------------+---------------------+ 2 rows in set (0.00 sec) Copy the code

To take effect permanently, you need to write it to the configuration file. For example, to change the time zone to GMT +8, add default_time_zone = ‘+8:00’ in the [mysqld] section of the configuration file.

3. Common time zone problems and how to avoid them

Setting the time zone improperly may cause various problems. Here are some common problems and solutions:

3.1 MySQL internal time is not Beijing time

If this problem occurs, check whether the time zone of the system time is correct. Then check whether the time_zone of the MySQL database is correct. You are advised to change the time_zone to +8:00.

3.2 The time difference between Java program access and database is 8 hours

This problem is most likely caused by the program time zone and database time zone inconsistency. Add serverTimezone=Asia/Shanghai to the JDBC connection string, and change time_zone to ‘+8:00’ for MySQL.

3.3 The difference between program time and database time is 13 hours or 14 hours

If an eight-hour difference isn’t surprising enough, a 13-hour difference is likely to confuse many people. The cause of this problem is that THE CST time zone negotiation is inconsistent between JDBC and MySQL. Because the CST time zone is a confusing time zone, it has four meanings:

  • Central Standard Time (USA) UTC-05:00 UTC-06:00
  • Central Standard Time (Australia) UTC+09:30
  • China Standard Time UTC+08:00
  • Cuba Standard Time UTC-04:00

In MySQL, if time_zone is the default SYSTEM value, the time zone is inherited as the SYSTEM time zone CST, which is internally considered as UTC+08:00. JDBC will consider CST to be Central American time, which results in a 13-hour difference, and another 14-hour difference if it’s winter.

The solution to this problem is simple. You can specify the time zone of the MySQL database explicitly, do not use the misleading CST, change time_zone to ‘+8:00’, and add serverTimezone=Asia/Shanghai to the JDBC connection string.

3.4 How can I Avoid time Zone Problems

You probably have some ideas in mind about how to avoid these time zone issues, but here are a few:

  1. First, ensure that the system time zone is accurate.
  2. The time zone is specified in the JDBC connection string and is consistent with the database time zone.
  3. You are advised to set time_zone to +8:00. Do not use CST, which is easily misunderstood.
  4. The time zone parameters of database instances in each environment must be the same.

The time_zone parameter in our database is the default SYSTEM value, and there is no inconsistency between the program time and the database time. Do you need to change time_zone to ‘+8:00’? In this case, it is recommended to change the time_zone to ‘+8:00’, especially when the TIMESTAMP field is frequently queried, because when time_zone=system, querying the TIMESTAMP field will call the system time zone for time zone conversion. It is protected by the global lock __libc_lock_lock, which may limit system performance in concurrent threads. If the value is changed to ‘+8:00’, the system time zone conversion will not be triggered.

Conclusion:

By the end of this article, do you have a better understanding of database time zones? I hope you found this article helpful, especially if you want to learn more about MySQL time zones. If you have encountered any other time zone related issues, please leave a comment.

WX

This article is formatted using MDNICE

– END –