Bug #101670 INSERT wrong timestamps using explicit time zone offset in time_zone with DST
Submitted: 18 Nov 2020 23:52 Modified: 24 Mar 2021 11:40
Reporter: Mattias Jonsson (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.22 OS:Ubuntu
Assigned to: CPU Architecture:Any

[18 Nov 2020 23:52] Mattias Jonsson 
Description:
Using time_zone = SYSTEM, when the system time zone has DST transitions together with inserting datetime literals with explicit time zone offset into TIMESTAMP column inserts the wrong time.

(Also the same if you build it on a mac, starting it with ./mtr --start 1st and then connect and using set @@time_zone = 'CET' instead of SYSTEM/DEFAULT)

How to repeat:
--------------
select 'SYSTEM/Default time zone is Europe/Amsterdam for me!' as 'Comment'
--------------

+------------------------------------------------------+
| Comment                                              |
+------------------------------------------------------+
| SYSTEM/Default time zone is Europe/Amsterdam for me! |
+------------------------------------------------------+
1 row in set (0.00 sec)

--------------
select @@version, @@time_zone
--------------

+-------------------------+-------------+
| @@version               | @@time_zone |
+-------------------------+-------------+
| 8.0.22-0ubuntu0.20.04.2 | SYSTEM      |
+-------------------------+-------------+
1 row in set (0.00 sec)

--------------
create table ts (id int unsigned not null auto_increment primary key, ts timestamp)
--------------

Query OK, 0 rows affected (0.71 sec)

--------------
select "Insert with explicit time zone offset does not work" as 'BUG'
--------------

+-----------------------------------------------------+
| BUG                                                 |
+-----------------------------------------------------+
| Insert with explicit time zone offset does not work |
+-----------------------------------------------------+
1 row in set (0.00 sec)

--------------
insert into ts (ts) values
('2020-10-24 23:40:00+00:00'),
('2020-10-25 00:00:00+00:00'),
('2020-10-25 00:20:00+00:00'),
('2020-10-25 00:40:00+00:00'),
('2020-10-25 01:00:00+00:00'),
('2020-10-25 01:20:00+00:00'),
('2020-10-25 01:40:00+00:00'),
('2020-10-25 02:00:00+00:00'),
('2020-10-25 02:20:00+00:00')
--------------

Query OK, 9 rows affected (0.17 sec)
Records: 9  Duplicates: 0  Warnings: 0

--------------
select "This looks OK from CEST/CET time zone" as 'BUG'
--------------

+---------------------------------------+
| BUG                                   |
+---------------------------------------+
| This looks OK from CEST/CET time zone |
+---------------------------------------+
1 row in set (0.00 sec)

--------------
select * from ts
--------------

+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2020-10-25 01:40:00 |
|  2 | 2020-10-25 02:00:00 |
|  3 | 2020-10-25 02:20:00 |
|  4 | 2020-10-25 02:40:00 |
|  5 | 2020-10-25 02:00:00 |
|  6 | 2020-10-25 02:20:00 |
|  7 | 2020-10-25 02:40:00 |
|  8 | 2020-10-25 03:00:00 |
|  9 | 2020-10-25 03:20:00 |
+----+---------------------+
9 rows in set (0.00 sec)

--------------
select "But the actual stored epoch are wrong!" as 'BUG'
--------------

+----------------------------------------+
| BUG                                    |
+----------------------------------------+
| But the actual stored epoch are wrong! |
+----------------------------------------+
1 row in set (0.00 sec)

--------------
select id, unix_timestamp(ts) from ts
--------------

+----+--------------------+
| id | unix_timestamp(ts) |
+----+--------------------+
|  1 |         1603582800 |
|  2 |         1603587600 |
|  3 |         1603588800 |
|  4 |         1603590000 |
|  5 |         1603587600 |
|  6 |         1603588800 |
|  7 |         1603590000 |
|  8 |         1603591200 |
|  9 |         1603592400 |
+----+--------------------+
9 rows in set (0.01 sec)

--------------
select "And the CAST AT TIME ZONE also shows the wrong values was stored!" as 'BUG'
--------------

+-------------------------------------------------------------------+
| BUG                                                               |
+-------------------------------------------------------------------+
| And the CAST AT TIME ZONE also shows the wrong values was stored! |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

--------------
select id, cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_datetime` from ts
--------------

+----+---------------------+
| id | utc_datetime        |
+----+---------------------+
|  1 | 2020-10-24 23:40:00 |
|  2 | 2020-10-25 01:00:00 |
|  3 | 2020-10-25 01:20:00 |
|  4 | 2020-10-25 01:40:00 |
|  5 | 2020-10-25 01:00:00 |
|  6 | 2020-10-25 01:20:00 |
|  7 | 2020-10-25 01:40:00 |
|  8 | 2020-10-25 02:00:00 |
|  9 | 2020-10-25 02:20:00 |
+----+---------------------+
9 rows in set (0.00 sec)

--------------
set time_zone = '+00:00'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
select @@version, @@time_zone
--------------

+-------------------------+-------------+
| @@version               | @@time_zone |
+-------------------------+-------------+
| 8.0.22-0ubuntu0.20.04.2 | +00:00      |
+-------------------------+-------------+
1 row in set (0.00 sec)

--------------
select "Same if time_zone is set to UTC!" as 'BUG'
--------------

+----------------------------------+
| BUG                              |
+----------------------------------+
| Same if time_zone is set to UTC! |
+----------------------------------+
1 row in set (0.00 sec)

--------------
select * from ts
--------------

+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2020-10-24 23:40:00 |
|  2 | 2020-10-25 01:00:00 |
|  3 | 2020-10-25 01:20:00 |
|  4 | 2020-10-25 01:40:00 |
|  5 | 2020-10-25 01:00:00 |
|  6 | 2020-10-25 01:20:00 |
|  7 | 2020-10-25 01:40:00 |
|  8 | 2020-10-25 02:00:00 |
|  9 | 2020-10-25 02:20:00 |
+----+---------------------+
9 rows in set (0.00 sec)

--------------
select id, unix_timestamp(ts) from ts
--------------

+----+--------------------+
| id | unix_timestamp(ts) |
+----+--------------------+
|  1 |         1603582800 |
|  2 |         1603587600 |
|  3 |         1603588800 |
|  4 |         1603590000 |
|  5 |         1603587600 |
|  6 |         1603588800 |
|  7 |         1603590000 |
|  8 |         1603591200 |
|  9 |         1603592400 |
+----+--------------------+
9 rows in set (0.00 sec)

--------------
select id, cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_datetime` from ts
--------------

+----+---------------------+
| id | utc_datetime        |
+----+---------------------+
|  1 | 2020-10-24 23:40:00 |
|  2 | 2020-10-25 01:00:00 |
|  3 | 2020-10-25 01:20:00 |
|  4 | 2020-10-25 01:40:00 |
|  5 | 2020-10-25 01:00:00 |
|  6 | 2020-10-25 01:20:00 |
|  7 | 2020-10-25 01:40:00 |
|  8 | 2020-10-25 02:00:00 |
|  9 | 2020-10-25 02:20:00 |
+----+---------------------+
9 rows in set (0.00 sec)

Suggested fix:
Make sure that datetime literals with explicit time zone offset is working correctly in INSERT and time_zone containing DST transisions.
[19 Nov 2020 6:00] MySQL Verification Team 
Hello Mattias,

Thank you for the report and feedback!

regards,
Umesh
[24 Mar 2021 11:40] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.25.

Inserting a datetime literal with an explicit time zone offset into a
TIMESTAMP column could produce the wrong time if time_zone=SYSTEM and
the system time zone has DST transitions.
[24 Apr 2021 16:05] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.26, not 8.0.25.