MySQL DATETIME 与 TIMESTAMP 类型切换时区数据修复建议

1 概述

通过实例展示 DATETIME 与 TIMESTAMP 数据类型在时区更改后数据的差异，供大家选择时间类型做个参考，以及出现类似问题如何修正数据。

2 创建演示表

CREATE TABLE `test_time_zone` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datetime1` DATETIME NULL,
  `timestamp1` TIMESTAMP NULL,
  `datetime2` DATETIME NULL,
  `timestamp2` TIMESTAMP NULL,
  PRIMARY KEY (`id`));

3 设置时区为 UTC

• 为了演示效果，临时修改时区配置，当连接断开后临时配置失效；

// 查询当前时区
show variables like "%time_zone%";

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+

// 设置为 UTC 时区
set time_zone = '+0:00';

show variables like "%time_zone%";

select now();

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+

+---------------------+
| now()               |
+---------------------+
| 2020-06-03 14:46:16 |
+---------------------+

4 插入测试数据

insert into test_time_zone 
(datetime1, timestamp1, datetime2, timestamp2) 
values 
(str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), now(), now());

5 查询结果

• now() 函数值依赖数据库时区配置；
• str_to_date() 函数不依赖数据库时区配置；

select * from test_time_zone \G;

*************************** 1. row ***************************
        id: 1
 datetime1: 2020-06-02 22:47:06
timestamp1: 2020-06-02 22:47:06
 datetime2: 2020-06-03 14:46:42
timestamp2: 2020-06-03 14:46:42

6 修改时区 UTC+8

set time_zone = '+8:00';

show variables like "%time_zone%";

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+

7 查询数据变化

• TIMESTAMP 类型的数据随着时区而改变；
• DATETIME 类型的数据始终保持不变；

select * from test_time_zone \G;

*************************** 1. row ***************************
        id: 1
 datetime1: 2020-06-02 22:47:06
timestamp1: 2020-06-03 06:47:06
 datetime2: 2020-06-03 14:46:42
timestamp2: 2020-06-03 22:46:42

8 插入测试数据

insert into test_time_zone (datetime1, timestamp1, datetime2, timestamp2) 
values 
(str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), str_to_date('2020-06-02 22:47:06','%Y-%m-%d %H:%i:%s'), now(), now());

select * from test_time_zone \G;

*************************** 1. row ***************************
        id: 1
 datetime1: 2020-06-02 22:47:06
timestamp1: 2020-06-03 06:47:06
 datetime2: 2020-06-03 14:46:42
timestamp2: 2020-06-03 22:46:42
*************************** 2. row ***************************
        id: 2
 datetime1: 2020-06-02 22:47:06
timestamp1: 2020-06-02 22:47:06
 datetime2: 2020-06-03 22:48:05
timestamp2: 2020-06-03 22:48:05

9 数据修复

update test_time_zone
set datetime1 = CONVERT_TZ(datetime1, '+0:00', '+8:00'),
    datetime2 = CONVERT_TZ(datetime2, '+0:00', '+8:00')
where id = 1;

select * from test_time_zone \G;

*************************** 1. row ***************************
        id: 1
 datetime1: 2020-06-03 06:47:06
timestamp1: 2020-06-03 06:47:06
 datetime2: 2020-06-03 22:46:42
timestamp2: 2020-06-03 22:46:42
*************************** 2. row ***************************
        id: 2
 datetime1: 2020-06-02 22:47:06
timestamp1: 2020-06-02 22:47:06
 datetime2: 2020-06-03 22:48:05
timestamp2: 2020-06-03 22:48:05

10 小结

• now() 函数值依赖数据库时区配置；
• str_to_date() 函数不依赖数据库时区配置；
• TIMESTAMP 类型的数据随着时区而改变，如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP 类型；
• DATETIME 类型的数据始终保持不变，时区修改后，数据需要修复；