Description:
If I specify a TIMESTAMP literal with a timezone offset in it that falls within the "ambiguous" period of time that occurs during the DST clock change in the fall, then MySQL returns erroneous results. This only occurs if the server's time_zone is set to a timezone that experiences a time shift for DST. In my case, this is the America/New_York timezone (Eastern Time).
What should be:
The literal "2021-11-07 01:59:59-04:00" should not be ambiguous since it has the timezone offset in it. It represents the second before a time shift in EDT time. The epoch timestamp for this is 1636264799.
What is:
When the server's time_zone is set to either EDT or EST, the above literal is interpreted as the epoch timestamp 1636268399, which is "2021-11-07 01:59:59-05:00".
However, if the server's time_zone is set to UTC, then the above literal is correctly interpreted as the epoch timestamp 1636264799.
How to repeat:
SELECT VERSION()
+-------------------------+
| VERSION() |
+-------------------------+
| 8.0.27-0ubuntu0.20.04.1 |
+-------------------------+
SELECT @@global.system_time_zone, UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00");
+---------------------------+---------------------------------------------+
| @@global.system_time_zone | UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00") |
+---------------------------+---------------------------------------------+
| EST | 1636268399 |
+---------------------------+---------------------------------------------+
SELECT TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00")
+---------------------------------------------------------------------------------+
| TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") |
+---------------------------------------------------------------------------------+
| -3598 |
+---------------------------------------------------------------------------------+
$ sudo timedatectl set-timezone "UTC"
$ sudo systemctl restart mysql.service
SELECT @@global.system_time_zone, UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00");
+---------------------------+---------------------------------------------+
| @@global.system_time_zone | UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00") |
+---------------------------+---------------------------------------------+
| UTC | 1636264799 |
+---------------------------+---------------------------------------------+
SELECT TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00")
+---------------------------------------------------------------------------------+
| TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") |
+---------------------------------------------------------------------------------+
| 2 |
+---------------------------------------------------------------------------------+
Description: If I specify a TIMESTAMP literal with a timezone offset in it that falls within the "ambiguous" period of time that occurs during the DST clock change in the fall, then MySQL returns erroneous results. This only occurs if the server's time_zone is set to a timezone that experiences a time shift for DST. In my case, this is the America/New_York timezone (Eastern Time). What should be: The literal "2021-11-07 01:59:59-04:00" should not be ambiguous since it has the timezone offset in it. It represents the second before a time shift in EDT time. The epoch timestamp for this is 1636264799. What is: When the server's time_zone is set to either EDT or EST, the above literal is interpreted as the epoch timestamp 1636268399, which is "2021-11-07 01:59:59-05:00". However, if the server's time_zone is set to UTC, then the above literal is correctly interpreted as the epoch timestamp 1636264799. How to repeat: SELECT VERSION() +-------------------------+ | VERSION() | +-------------------------+ | 8.0.27-0ubuntu0.20.04.1 | +-------------------------+ SELECT @@global.system_time_zone, UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00"); +---------------------------+---------------------------------------------+ | @@global.system_time_zone | UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00") | +---------------------------+---------------------------------------------+ | EST | 1636268399 | +---------------------------+---------------------------------------------+ SELECT TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") +---------------------------------------------------------------------------------+ | TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") | +---------------------------------------------------------------------------------+ | -3598 | +---------------------------------------------------------------------------------+ $ sudo timedatectl set-timezone "UTC" $ sudo systemctl restart mysql.service SELECT @@global.system_time_zone, UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00"); +---------------------------+---------------------------------------------+ | @@global.system_time_zone | UNIX_TIMESTAMP("2021-11-07 01:59:59-04:00") | +---------------------------+---------------------------------------------+ | UTC | 1636264799 | +---------------------------+---------------------------------------------+ SELECT TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") +---------------------------------------------------------------------------------+ | TIMESTAMPDIFF(SECOND, "2021-11-07 01:59:59-04:00", "2021-11-07 01:00:01-05:00") | +---------------------------------------------------------------------------------+ | 2 | +---------------------------------------------------------------------------------+