expression: Non-existing local timestamps during DST spring forward got converted to 0 instead of getting the transition timestamp

[kennytm]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set time_zone = 'Europe/Vilnius';
select unix_timestamp('2020-03-29 03:45:00');

2. What did you expect to see? (Required)

On MySQL 8.0, after the server time zone got populated:

mysql> select unix_timestamp('2020-03-29 03:45:00');
+---------------------------------------+
| unix_timestamp('2020-03-29 03:45:00') |
+---------------------------------------+
|                            1585443600 |
+---------------------------------------+
1 row in set (0.00 sec)

(Note: 1,585,443,600 = 2020-03-29 01:00:00 UTC, the instant when the spring forward happens)

3. What did you see instead (Required)

mysql> select unix_timestamp('2020-03-29 03:45:00');
+---------------------------------------+
| unix_timestamp('2020-03-29 03:45:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.15 sec)

4. What is your TiDB version? (Required)

Release Version: v5.2.1
Edition: Community
Git Commit Hash: cd8fb24c5f7ebd9d479ed228bb41848bd5e97445
Git Branch: heads/refs/tags/v5.2.1
UTC Build Time: 2021-09-08 02:32:56
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[morgo]

This issue is critical to any user who has their server configured in a timezone which uses DST (it's not that uncommon in North America and Europe).

Consider the case that they use a timestamp to track the order_date of orders, and then have another script that tries to find orders in the last day. Because some of the orders were inserted with a zero date, they would be incorrectly missed.

[mjonss]

/assign

[mjonss]

Notice that the specified time is non-existing (i.e. during Daylight Saving Time Leap forward):

$ zdump -v -c 2019,2021 Europe/Vilnius
Europe/Vilnius  -9223372036854775808 = NULL
Europe/Vilnius  -9223372036854689408 = NULL
Europe/Vilnius  Sun Mar 31 00:59:59 2019 UT = Sun Mar 31 02:59:59 2019 EET isdst=0 gmtoff=7200
Europe/Vilnius  Sun Mar 31 01:00:00 2019 UT = Sun Mar 31 04:00:00 2019 EEST isdst=1 gmtoff=10800
Europe/Vilnius  Sun Oct 27 00:59:59 2019 UT = Sun Oct 27 03:59:59 2019 EEST isdst=1 gmtoff=10800
Europe/Vilnius  Sun Oct 27 01:00:00 2019 UT = Sun Oct 27 03:00:00 2019 EET isdst=0 gmtoff=7200
Europe/Vilnius  Sun Mar 29 00:59:59 2020 UT = Sun Mar 29 02:59:59 2020 EET isdst=0 gmtoff=7200
Europe/Vilnius  Sun Mar 29 01:00:00 2020 UT = Sun Mar 29 04:00:00 2020 EEST isdst=1 gmtoff=10800
Europe/Vilnius  Sun Oct 25 00:59:59 2020 UT = Sun Oct 25 03:59:59 2020 EEST isdst=1 gmtoff=10800
Europe/Vilnius  Sun Oct 25 01:00:00 2020 UT = Sun Oct 25 03:00:00 2020 EET isdst=0 gmtoff=7200
Europe/Vilnius  9223372036854689407 = NULL
Europe/Vilnius  9223372036854775807 = NULL

[kennytm]

while DST is common, I doubt if it is really critical to support parsing non-existing local timestamps 🤔

[dveeden]

while DST is common, I doubt if it is really critical to support parsing non-existing local timestamps thinking

I think failing (returning NULL) would be acceptable, giving a wrong result is more problematic.

[kennytm]

MySQL 8 does return 0 for unsupported input though.

mysql> select unix_timestamp('2039-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('2039-01-01 00:00:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

[dveeden]

@kennytm I'm ok with returning 0 in places where MySQL 8.0 also does this. However I would rather see us support timestamps past 2038.

[kennytm]

well supporting timestamp outside of 1970–2038 would be a different issue (#30133) 🙃

[tiancaiamao]

I don't this issue need a severity/critical tag, the input data is problematic to trigger it.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[mjonss]

I created a proposal for golang time package:
golang/go#50062