Author: Hu Chengqing

Member of aikesheng DBA team, good at fault analysis and performance optimization, personal blog: https://www.jianshu.com/u/a95... , welcome to the discussion.

Source: original contribution

*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.

Are you still bothered by the following problems:

What time zone should be set in the MySQL installation specification?

The time read by JAVA application is 14 hours different from Beijing time. Why? How?

For businesses that have been running for some time, will modifying the time zone of MySQL affect the stored time type data?

Is it possible to cause wrong time zone of time type data when migrating data?

...

After reading this article, you can solve all the doubts above. The first is the startup parameters and system variables related to the time zone.

Startup parameters & system variables

If you want to specify the time zone when MySQL starts, you should use the startup parameter: default time zone. Example:

--Method 1: add in the startup command
mysqld --default-time-zone='+08:00' &

--Method 2: add in the configuration file
[mysqld]
default-time-zone='+08:00'

After startup, we can see the system variable that controls the time zone, where time_ The zone variable controls the time zone, which can be modified by the set command when MySQL is running (Note: it cannot be written in my.cnf):

--see
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)

--Modify the global time zone and all the created and newly created time zones session Will be modified
set global time_zone='+00:00';

--Modify current session Time zone
set session time_zone='+00:00';

The available values of startup parameters and system variables follow the same format:

• 'SYSTEM 'indicates that the SYSTEM time is used
• Offset from UTC time, such as' + 08:00 'or' - 6:00 '
• The name of a time zone, such as' Europe/Helsinki ',' Asia/Shanghai 'or' UTC ', if the time zone information has been imported into the MySQL database, otherwise an error will be reported. Import method: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/mysqld.sock mysql

system_ time_ The zone variable has only a global value and no session value and cannot be dynamically modified. When MySQL starts, it will try to automatically determine the time zone of the server and use it to set the system_ time_ The zone system variable, after which the value remains unchanged. When time_ When zone ='system ', this is the time zone used. In the example, time_ The zone is the CST, and the CST on red hat is the East eighth District:

mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

--You can see that on the current operating system CST namely +08:00 time zone
[root@localhost ~]# date -R
Thu, 02 Dec 2021 17:41:46 +0800
[root@localhost ~]# date
2021 Thursday, December 2, 2007 17:41:49 CST

What does time zone affect

To sum up, there are two points:

1. The return values of the now() and CURTIME() system functions are affected by the time zone of the current session

Not only select now(), but also insert Values (now ()), and default current of the field_ The timestamp property is also affected by this:

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

mysql> select now(),CURTIME();
+---------------------+-----------+
| now()               | CURTIME() |
+---------------------+-----------+
| 2021-12-02 08:45:33 | 08:45:33  |
+---------------------+-----------+
1 row in set (0.00 sec)

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

mysql> select now(),CURTIME();
+---------------------+-----------+
| now()               | CURTIME() |
+---------------------+-----------+
| 2021-12-02 16:45:39 | 16:45:39  |
+---------------------+-----------+
1 row in set (0.00 sec)

2. The data stored in the timestamp data type field is affected by the time zone

The timestamp data type will store the time zone information of the current session. When reading, it will be converted according to the time zone of the current session; The value inserted in the datetime data type is the value read again, which is not affected by the time zone. It can also be understood that the stored data will not change, but the timestamp type data will be converted according to the time zone when reading:

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

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

mysql> insert into t values('2021-12-02 16:45:39','2021-12-02 16:45:39');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-12-02 16:45:39 | 2021-12-02 16:45:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

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

mysql> select * from t;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-12-02 08:45:39 | 2021-12-02 16:45:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

conclusion

All the bright things about the time zone are above. The trouble we mentioned earlier is the experience in the dark.

1. What time zone should be set in the MySQL installation specification?

For domestic business, in my CNF writes default time zone = '+ 08:00' `, and other regions and development confirm to take the corresponding time zone.

Why not set it to system? Using the system time seems to be a good choice, which is easy. There are two reasons why it is not recommended:

• The setting of the operating system may not be managed by the DBA. What if others do not set the correct system time zone? Giving your back to someone else may be a little cold;
• There is one more layer of system call, which leads to performance loss.

2. The time read by Java application is 14 hours different from Beijing time. Why? How?

This is usually because the time zone property is not set for the connection in the JDBC parameters (specified with serverTimezone parameter), and MySQL does not have a global time zone set, so MySQL uses the system time zone by default, that is, CST. In this way, the session time_zone of the application connection with MySQL is CST. We mentioned earlier that CST is the + 08:00 time zone on RedHat, but in fact it can represent four time zones:

• Central Standard Time (USA) UT-6:00 US standard time
• Central Standard Time (Australia) UT+9:30 Australian standard time
• China Standard Time UT+8:00
• Cuba Standard Time UT-4:00 Cuba standard time

JDBC uses us standard time when parsing CST, which will lead to time zone error. The solution is also simple: first, comply with the specifications just mentioned above and set the '+ 08:00' time zone for MySQL display; Second, JDBC sets the correct serverTimezone.

3. For businesses that have been running for some time, will modifying the time zone of MySQL affect the stored time type data?

No, it only affects the reading of timestamp data type. I have to mention here, why use timestamp? Isn't it nice to use datetime? It has a larger range. In fact, the difference in storage space is very small. Hurry to add it to the development specification.

4. Is it possible to cause time zone error of time type data when migrating data?

This is true. It is still for the timestamp data type, such as exporting csv format data using mysqldump. By default, this export method will use the UTC time zone to read the timestamp data, which means that the session must be set manually during import time_ Zone = '+ 00:00' to ensure accurate time:

--take test.t Export as csv
mysqldump -S /data/mysql/data/3306/mysqld.sock --single-transaction \
--master-data=2 -t -T /data/backup/test3 --fields-terminated-by=',' test t

--View exported data
cat /data/backup/test3/t.txt
2021-12-02 08:45:39,2021-12-02 16:45:39

How to avoid? mysqldump also provides a parameter -- skip TZ UTC, which means that the connection for exporting data does not set UTC time zone and uses MySQL global time_ The value of the zone system variable.

In fact, the UTC time zone is also used by default when exporting SQL files from mysqldump, and session time is added to the header of the exported SQL file_ Zone information, which can ensure that the same time zone is used when importing and exporting the SQL file, so as to ensure that the time zone of the data is correct (obviously, the exported csv file cannot carry this information). It should be noted that the -- compact parameter will remove all header information of the SQL file, so it must be remembered that the: - compact parameter must be used together with -- skip TZ UTC.

-- MySQL dump 10.13  Distrib 8.0.18, for linux-glibc2.12 (x86_64)
--
-- Host: 10.186.17.104    Database: sbtest
-- ------------------------------------------------------
...
/*!40103 SET TIME_ZONE='+00:00' */;
...