详解MySQL中timestamp和datetime时区问题导致做DTS遇到的坑
时间：2022-05-18 08:43:09|栏目：Mysql|点击： 次
MySQL中如何表示当前时间？
其实，表达方式还是蛮多的，汇总如下：
| Data Type | “Zero” Value |
|---|---|
DATE |
'0000-00-00' |
TIME |
'00:00:00' |
DATETIME |
'0000-00-00 00:00:00' |
TIMESTAMP |
'0000-00-00 00:00:00' |
YEAR |
0000 |
datetime和timestamp这两种类型都是用于表示YYYY-MM-DD HH:MM:SS 这种年月日时分秒格式的数据，但两者还是有些许不同之处的。
结论
- timestamp实际存储的是1970-01-01 00:00:00 UTC到目前的秒数占4字节(时间精度为毫秒和纳秒时会占用更多字节)，故相当于是带时区的时间，通过设置会话的时区，会自动转换为设置的时区的时间
- datetime存储的就是格式化后的字符串类似'2021-12-05 13:27:53.957033'，不携带时区信息，在UTC和CST时区查询到的结果是一致的，例如在CST时区写入的'2021-12-05 13:27:53.957033'，但是在UTC时区查询到的还是'2021-12-05 13:27:53.957033'，如果没做时区转换，就相当于是直接将CST时间映射为UTC时间，但是实际上UTC时间比CST时间慢8个小时
验证
环境准备，简而言之就是存在一张表有timestamp字段和datetime字段，且当前服务端为CST时区
mysql> show create table test_time\G; *************************** 1. row *************************** Table: test_time Create Table: CREATE TABLE `test_time` ( `id` int NOT NULL AUTO_INCREMENT, `ts` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `dt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.01 sec)
插入一条数据，当前CST时区下ts和dt结果相同
mysql> select * from test_time; Empty set (0.00 sec) mysql> insert into test_time() values(); Query OK, 1 row affected (0.00 sec) mysql> select * from test_time; +----+----------------------------+----------------------------+ | id | ts | dt | +----+----------------------------+----------------------------+ | 3 | 2021-12-05 15:04:13.293949 | 2021-12-05 15:04:13.293949 | +----+----------------------------+----------------------------+ 1 row in set (0.00 sec)
将会话的时区设置为UTC时区再次查询，ts由于从CST时区变为UTC时区查询到的结果比之前慢8个小时，由于dt不带时区信息，结果不变
mysql> set time_zone='+00:00'; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_time; +----+----------------------------+----------------------------+ | id | ts | dt | +----+----------------------------+----------------------------+ | 3 | 2021-12-05 07:04:13.293949 | 2021-12-05 15:04:13.293949 | +----+----------------------------+----------------------------+ 1 row in set (0.01 sec)
从刚刚insert产生的binlog中也有体现，ts在binlog中存储为时间戳(从1970-01-01 00:00:00 UTC到目前的秒数)相当于带UTC时区信息，dt为不带时区信息，结果为格式化后的字符串2021-12-05 15:04:13.293949，主要关注倒数第4第5行，@2=1638687853.293949表示ts字段的值， @3='2021-12-05 15:04:13.293949'表示dt字段的值
[mysql %] mysqlbinlog -v --base64-output=decode-rows ./mysqlbin.000012 ... ... SET @@SESSION.GTID_NEXT= '1cf4493a-dafd-11eb-944c-4016af29c14c:1416767'/*!*/; # at 14220 #211205 15:04:13 server id 1 end_log_pos 14308 CRC32 0x1fd913a3 Query thread_id=137 exec_time=0 error_code=0 SET TIMESTAMP=1638687853.293949/*!*/; BEGIN /*!*/; # at 14308 #211205 15:04:13 server id 1 end_log_pos 14368 CRC32 0xbb8937fb Table_map: `testa`.`test_time` mapped to number 121 # at 14368 #211205 15:04:13 server id 1 end_log_pos 14423 CRC32 0x2e0a3baa Write_rows: table id 121 flags: STMT_END_F ### INSERT INTO `testa`.`test_time` ### SET ### @1=3 ### @2=1638687853.293949 ### @3='2021-12-05 15:04:13.293949' # at 14423 #211205 15:04:13 server id 1 end_log_pos 14454 CRC32 0x68cee280 Xid = 1416 COMMIT/*!*/;
坑
- 如果在做DTS相关项目时，使用解析MySQL binlog的开源工具，例如github.com/go-mysql-org/go-mysql，如果配置了parseTime=true会将timestamp类型字段解析为Local时间，将datetime类型解析为UTC时间，也可配置为false获取到的就是字符串(timestamp已转换为会话时区的时间，datetime就是binlog中原生的字符串)自己解析，如果parseTime=true且不是使用的UTC时间插入的datetime字段，理论上拿到的时间已经不正确了，相当于直接将CST的2021-12-05 15:04:13.293949转换为了UTC的2021-12-05 15:04:13.293949，实际上应该转换为UTC的2021-12-05 07:04:13.293949才正确
- 如果刚好业务需求中有time.Now()获取的Local时间和datetime类型字段比较的场景，需要注意时区问题，或者将时间都去掉时区，转换为格式化后的字符串相比较
- 由于datetime本身就不带时区信息，除了转换UTC时间，也没有更好的选择，所以很坑！
上一篇：mysql常用函数实例总结【聚集函数、字符串、数值、时间日期处理等】
栏 目：Mysql
本文标题：详解MySQL中timestamp和datetime时区问题导致做DTS遇到的坑
本文地址：http://www.codeinn.net/misctech/202221.html
