Detailed explanation of the pitfalls of DTS caused by the timestamp and datetime time zone issues in MySQL

How to represent the current time in MySQL?

In fact, there are many ways to express it, which are summarized as follows:

Both datetime and timestamp types are used to represent data in the format of YYYY-MM-DD HH:MM:SS, but there are some differences between the two.

in conclusion

• timestamp actually stores the number of seconds from 1970-01-01 00:00:00 UTC to the present, which takes up 4 bytes (more bytes will be used when the time precision is milliseconds and nanoseconds), so it is equivalent to time with a time zone. By setting the time zone of the session, it will automatically be converted to the time of the set time zone.
• Datetime stores a formatted string similar to '2021-12-05 13:27:53.957033', which does not carry time zone information. The results queried in the UTC and CST time zones are consistent. For example, '2021-12-05 13:27:53.957033' is written in the CST time zone, but queried in the UTC time zone is still '2021-12-05 13:27:53.957033'. If the time zone conversion is not performed, it is equivalent to directly mapping the CST time to the UTC time, but in fact the UTC time is 8 hours slower than the CST time.

verify

Environment preparation, in short, there is a table with timestamp field and datetime field, and the current server is in CST 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. The results of ts and dt are the same in the current CST time zone.

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 session time zone to UTC and query again. The result of ts query is 8 hours slower than before because the time zone changes from CST to UTC. Since dt does not carry 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)

This is also reflected in the binlog generated by the insert operation. ts is stored in the binlog as a timestamp (the number of seconds from 1970-01-01 00:00:00 UTC to the present), which is equivalent to the UTC time zone information. dt is without the time zone information. The result is the formatted string 2021-12-05 15:04:13.293949. Focus on the fourth and fifth lines from the bottom. @2=1638687853.293949 represents the value of the ts field, and @3='2021-12-05 15:04:13.293949' represents the value of the dt 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 use an open source tool for parsing MySQL binlog when doing DTS-related projects, such as github.com/go-mysql-org/go-mysql, and configure parseTime=true, the timestamp type field will be parsed as Local time, and the datetime type will be parsed as UTC time. It can also be configured as false to get a string (timestamp has been converted to the time in the session time zone, and datetime is the native string in binlog). Parse it yourself. If parseTime=true and the datetime field is not inserted using UTC time, theoretically the time obtained is incorrect, which is equivalent to directly converting CST's 2021-12-05 15:04:13.293949 to UTC's 2021-12-05 15:04:13.293949. In fact, it should be converted to UTC's 2021-12-05 07:04:13.293949 to be correct.
• If the business needs require comparison between the Local time obtained by time.Now() and a datetime type field, you need to pay attention to the time zone issue, or remove the time zone from the time and convert it into a formatted string for comparison.
• Since datetime itself does not carry time zone information, there is no better choice than converting to UTC time, so it is a pitfall!

This concludes this article on explaining in detail the pitfalls of DTS caused by the time zone issues of timestamp and datetime in MySQL. For more information about MySQL timestamp and datetime pitfalls, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!