Why does MySQL return an incorrect TIMESTAMPDIFF() result around a daylight savings time change?

Question

This popped up in the recent time change from EDT to EST a couple days ago. Note that our DB is in ET, not UTC, and our DB team is hesitant to change that because that's the way it has been for many years.

$ 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 |
+-------------------------+---------------------------+-------------+-------------+

So notice that even though the time offset is specifically given in the two timestamps, the result on the 7th (when daylight savings time went into effect) is off by an hour.

This doesn't happen if the system is set to 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 |
+-------------------------+---------------------------+-------------+-------------+

Is this expected behavior? Shouldn't the server timezone have no effect on timestamps that have an explicit offset specified?

---

Update

As requested, I added info to show how the problem manifests itself in a "real" system with stored data. This shows a query that incorrectly returns a 5 second old entry as occurring in the future.

$ 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 |
+---------------------+----------------------------------+

However, if I just change the date to the 8th instead of the 7th, then I get a completely different result:

$ 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

After further testing, I'm pretty convinced that this is a bug in MySQL. I have submitted the following two bug reports, and they have been Verified as reproducible by the development team:

https://bugs.mysql.com/105515

https://bugs.mysql.com/105517