为什么MySQL返回关于夏时制更改的不正确的TIMESTAMPDIFF()结果？

Question

这在几天前从美国东部时间到东部时间的变化中突然出现。请注意，我们的DB位于ET中，而不是UTC，而我们的DB团队对改变这一点犹豫不决，因为多年来都是这样的。

$ mysql --login-path=local dst_test -e '                 
SELECT VERSION(),
@@global.system_time_zone,
TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") as "Diff on 7th",
TIMESTAMPDIFF(SECOND, "2021-11-08 01:59:59-04:00", "2021-11-08 01:00:01-05:00") as "Diff on 8th";
'
+-------------------------+---------------------------+-------------+-------------+
| VERSION()               | @@global.system_time_zone | Diff on 7th | Diff on 8th |
+-------------------------+---------------------------+-------------+-------------+
| 8.0.27-0ubuntu0.20.04.1 | EST                       |       -3598 |           2 |
+-------------------------+---------------------------+-------------+-------------+

所以请注意，尽管时间偏移是在两个时间戳中给出的，但7号(夏时制生效时)的结果却是差了一个小时。

如果系统设置为UTC，则不会发生这种情况：

$ sudo timedatectl set-timezone "UTC"
$ sudo systemctl restart mysql.service 
$ mysql --login-path=local dst_test -e '
SELECT VERSION(),
@@global.system_time_zone,
TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") as "Diff on 7th",
TIMESTAMPDIFF(SECOND, "2021-11-08 01:59:59-04:00", "2021-11-08 01:00:01-05:00") as "Diff on 8th";
'
+-------------------------+---------------------------+-------------+-------------+
| VERSION()               | @@global.system_time_zone | Diff on 7th | Diff on 8th |
+-------------------------+---------------------------+-------------+-------------+
| 8.0.27-0ubuntu0.20.04.1 | UTC                       |           2 |           2 |
+-------------------------+---------------------------+-------------+-------------+

这是预期的行为吗？服务器时区不应该对指定了显式偏移量的时间戳没有影响吗？

更新

按照要求，我添加了信息，以显示问题是如何在存储数据的“真实”系统中表现出来的。这显示了一个查询，该查询不正确地返回将来发生的一个5秒的旧条目。

$ sudo date -s "11/07/21 01:59:59" &&
mysql --login-path=local dst_test -e '
SELECT @@global.system_time_zone;
DROP TABLE IF EXISTS t;
CREATE TABLE t(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t() VALUES();
' &&
sleep 5 &&
mysql --login-path=local dst_test -e '
SELECT @@global.system_time_zone;
SELECT ts, TIMESTAMPDIFF(SECOND, ts, NOW()) FROM t;
'
Sun 07 Nov 2021 01:59:59 AM EDT
+---------------------------+
| @@global.system_time_zone |
+---------------------------+
| EDT                       |
+---------------------------+
+---------------------------+
| @@global.system_time_zone |
+---------------------------+
| EST                       |
+---------------------------+
+---------------------+----------------------------------+
| ts                  | TIMESTAMPDIFF(SECOND, ts, NOW()) |
+---------------------+----------------------------------+
| 2021-11-07 01:59:59 |                            -3595 |
+---------------------+----------------------------------+

但是，如果我把日期改为8号而不是7号，结果就完全不同了：

$ sudo date -s "11/08/21 01:59:59" &&
mysql --login-path=local dst_test -e '
SELECT @@global.system_time_zone;
DROP TABLE IF EXISTS t;
CREATE TABLE t(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t() VALUES();
' &&
sleep 5 &&
mysql --login-path=local dst_test -e '
SELECT @@global.system_time_zone;
SELECT ts, TIMESTAMPDIFF(SECOND, ts, NOW()) FROM t;
'
Mon 08 Nov 2021 01:59:59 AM EST
+---------------------------+
| @@global.system_time_zone |
+---------------------------+
| EST                       |
+---------------------------+
+---------------------------+
| @@global.system_time_zone |
+---------------------------+
| EST                       |
+---------------------------+
+---------------------+----------------------------------+
| ts                  | TIMESTAMPDIFF(SECOND, ts, NOW()) |
+---------------------+----------------------------------+
| 2021-11-08 01:59:59 |                                5 |
+---------------------+----------------------------------+

Answer 1

经过进一步的测试，我确信这是MySQL中的一个bug。我提交了以下两份bug报告，并已被验证为可由开发团队复制：

https://bugs.mysql.com/105515

https://bugs.mysql.com/105517