Bug #105515 Timezone offsets in TIMESTAMP literals broken in DST timezone

Submitted: 10 Nov 2021 13:10 Modified: 10 Nov 2021 13:59
Reporter: Dan Hentschel Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0 OS:Any (20.0.4.1)
Assigned to: CPU Architecture:Any

[10 Nov 2021 13:10] Dan Hentschel 
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 |
+---------------------------------------------------------------------------------+
[10 Nov 2021 13:59] MySQL Verification Team 
Hi Mr. Hentschel,

Actually, after further analysis and testing , we conclude that you are quite correct. This is indeed a bug. It can also be repeated on any platform and any CPU.

Thank you for your contribution.

Verified as reported.
[23 Oct 2023 12:28] Alexander Barker 
Any updates on this bug? 

Is there any known/official workarounds?
[23 Oct 2023 13:34] MySQL Verification Team 
Hi Mr. Hentschel,

Thank you for your message.

No, there are no news on this front and we do not know when will there be one.

Each of the teams has it's own bug schedule and that schedule changes on weekly basis, so nobody can give you a reliable answer.

Regarding workarounds, if there is any it is on the following page:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.