Preface
What is the use of MySQL time zone parameter time_zone? What is the effect of modifying it?
How to set this parameter will be introduced in detail in this article.
1. Time zone parameters affect
The time_zone parameter affects the properties of the MySQL system functions and fields of DEFAULT CURRENT_TIMESTAMP.
To query the current time zone, +8:00 represents the domestic time zone:
root@mysql 15:08: [(none)]>select @@time_zone; +-------------+ | @@time_zone | +-------------+ | +08:00 | +-------------+
Query the current time:
root@mysql 15:09: [(none)]>select now(); +---------------------+ | now() | +---------------------+ | 2024-12-12 15:09:44 | +---------------------+
Modify the time zone to UTC -8:00 US time:
root@mysql 15:09: [(none)]>set global time_zone = '-08:00'; Query OK, 0 rows affected (0.00 sec)
Query the current time:
root@mysql 15:09: [(none)]>select now(); +---------------------+ | now() | +---------------------+ | 2024-12-11 23:09:55 | +---------------------+
In addition, it should be noted that the timestamp type will change with the value of time_zone, while the datetime type will not. Please see the demonstration below.
Confirm the current time_zone parameter value:
select @@time_zone; +-------------+ | @@time_zone | +-------------+ | +08:00 | +-------------+
Create a test table structure. The difference between the two tables is that created_at and updated_at are datetime and timestamp types respectively.
CREATE TABLE `api_datetime` ( `id` bigint(64) NOT NULL AUTO_INCREMENT, user varchar(10), `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time', `enabled` bit(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8; CREATE TABLE `api_timestamp` ( `id` bigint(64) NOT NULL AUTO_INCREMENT, user varchar(10), `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time', `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time', `enabled` bit(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
Simulated data insertion:
insert into api_timestamp(user, enabled) values ('+08:00', b'1');
insert into api_datetime(user, enabled) values ('+08:00', b'1');Query table data:
root@mysql 16:21: [test]>select user,created_at, updated_at from api_datetime; +--------+---------------------+---------------------+ | user | created_at | updated_at | +--------+---------------------+---------------------+ | +08:00 | 2024-12-12 16:20:34 | 2024-12-12 16:20:34 | +--------+---------------------+---------------------+ 1 row in set (0.00 sec) root@mysql 16:21: [test]>select user,created_at, updated_at from api_timestamp; +--------+---------------------+---------------------+ | user | created_at | updated_at | +--------+---------------------+---------------------+ | +08:00 | 2024-12-12 16:20:33 | 2024-12-12 16:20:33 | +--------+---------------------+---------------------+
Modify the time_zone parameter value to -8:00:
set global time_zone = '-8:00';
Insert test data:
insert into api_timestamp(user, enabled) values ('-08:00', b'1');
insert into api_datetime(user, enabled) values ('-08:00', b'1');root@mysql 16:25: [test]>select user,created_at, updated_at from api_datetime; +--------+---------------------+---------------------+ | user | created_at | updated_at | +--------+---------------------+---------------------+ | +08:00 | 2024-12-12 16:20:34 | 2024-12-12 16:20:34 | | -08:00 | 2024-12-12 00:25:52 | 2024-12-12 00:25:52 | +--------+---------------------+---------------------+ 2 rows in set (0.00 sec) root@mysql 16:25: [test]>select user,created_at, updated_at from api_timestamp; +--------+---------------------+---------------------+ | user | created_at | updated_at | +--------+---------------------+---------------------+ | +08:00 | 2024-12-12 00:20:33 | 2024-12-12 00:20:33 | | -08:00 | 2024-12-12 00:25:52 | 2024-12-12 00:25:52 | +--------+---------------------+---------------------+ 2 rows in set (0.00 sec)
From the above test, we found that if the field is set to CURRENT_TIMESTAMP, whether it is datetime or timestamp type, it will be affected by the time_zone parameter. However, the historical data of datetime type will not be affected, and the historical data of timestamp type will change with the adjustment of time_zone.
2. How to set it up
It is recommended to write it directly in MySQL configuration file, and it needs to be restarted to take effect.
[mysqld] default-time-zone='+08:00'
This parameter defaults to SYSTEM. This parameter represents the value of this parameter, taken from the operating system's time zone settings. However, it is still recommended to set it in the MySQL parameter file, because the operating system may not be completely managed by DBA. If someone suddenly adjusts it, it may cause online problems.
In addition, if time_zone uses the default system value to indicate the default operating system time zone, each time the time is calculated through the time zone, the underlying system function __tz_convert() of the operating system needs to be called, and this function requires an additional locking operation to ensure that the operating system time zone is not modified at this time. High concurrency will lead to TIMESTAMP type tables and operations, and performance will be degraded.
3. Field type selection
In business, try to use the datetime type to store time. In addition to the fact that historical data will not change with the time zone, there is also a maximum value limit problem.
TIMESTAMP stores 1970-01-01 00:00:00’ The number of milliseconds to the present. TIMESTAMP takes up 4 bytes, so the upper limit of its storage time can only reach 2038-01-19 03:14:07. It is not far from now, and it needs to be paid attention to. The business will face another problem similar to the millennium bug.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.