Explain in detail the pitfalls encountered in DTS due to the time zone problems of timestamp and datetime in MySQL

Source of the article ： Learn through http://www.bdgxy.com/

MySQL How to represent the current time in ？

Actually , There are many ways of expression , The summary is as follows ：

datetime and timestamp Both types are used to represent YYYY-MM-DD HH:MM:SS This kind of data in the format of year, month, day, hour, minute and second , But there are some differences between the two .

Conclusion

• timestamp What's actually stored is 1970-01-01 00:00:00 UTC The number of seconds so far accounts for 4 byte ( When the time precision is milliseconds and nanoseconds, it will occupy more bytes ), Therefore, it is equivalent to the time with time zone , By setting the time zone of the session , It will be automatically converted to the set time zone
• datetime Stored is the formatted string, similar to '2021-12-05 13:27:53.957033', Do not carry time zone information , stay UTC and CST The results of time zone query are consistent , For example, in CST Time zone write '2021-12-05 13:27:53.957033', But in UTC The time zone query still finds '2021-12-05 13:27:53.957033', If there is no time zone conversion , It's equivalent to directly CST Time mapped to UTC Time , But actually UTC Time ratio CST Time is slow 8 Hours

verification

Environmental preparation , In short, there is a table with timestamp Fields and datetime Field , And the current server is CST The time zone

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)

Insert a piece of data , At present CST In time zone ts and dt The result is the same

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)

Set the time zone of the session to UTC Check the time zone again ,ts As a result of CST Time zone changed to UTC The result of time zone query is slower than before 8 Hours , because dt No time zone information , The result remains unchanged.

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)

From just now on insert Produced binlog There is also a reflection of ,ts stay binlog Stored as timestamp in ( from 1970-01-01 00:00:00 UTC The number of seconds so far ) It's equivalent to taking UTC Time zone information ,dt No time zone information , The result is a formatted string 2021-12-05 15:04:13.293949, Focus on the penultimate 4 The first 5 That's ok ,@2=1638687853.293949 Express ts Value of field , @3='2021-12-05 15:04:13.293949' Express dt Value of field

[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/*!*/;

pit

• If you're doing DTS When related projects , Use parsing MySQL binlog Open source tools for , for example github.com/go-mysql-org/go-mysql, If the parseTime=true Will timestamp The type field resolves to Local Time , take datetime Type resolution as UTC Time , It can also be configured as false What you get is a string (timestamp Time converted to session time zone ,datetime Namely binlog The original string ) Self analysis , If parseTime=true And not used UTC Time inserted datetime Field , In theory, the time is not correct , Equivalent to directly CST Of 2021-12-05 15:04:13.293949 Convert to UTC Of 2021-12-05 15:04:13.293949, In fact, it should be converted to UTC Of 2021-12-05 07:04:13.293949 That's right.
• If you happen to have time.Now() Acquired Local Time and datetime Type field comparison scenario , Pay attention to the time zone , Or remove all the time from the time zone , Convert to a formatted string and compare
• because datetime There is no time zone information in itself , Except for conversion UTC Time , There is no better choice , So it's very boring ！

Here is a detailed explanation of MySQL in timestamp and datetime Time zone problems lead to DTS The article on the pit encountered is introduced here , More about MySQL timestamp and datetime Please search for the content of the tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！