MySQL Bugs: #106763: China's DST is not right

Bug #106763	China's DST is not right
Submitted:	17 Mar 2022 16:58	Modified:	28 Mar 2022 8:16
Reporter:	wei lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	china, DST, timezone

Description:
China's 1988 spring-forward transition was on April 17, not April 10. Its DST transitions in 1986/91 were at 02:00, not 00:00.

How to repeat:
 CREATE TABLE `t5` (
  `id` int DEFAULT NULL,
  `dt1` datetime DEFAULT NULL,
  `dt2` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB;

set time_zone='+0:00';
insert into t5(id,dt1,dt2) values(1,'1987-04-11 18:30:00','1987-04-11 18:30:00'); 
insert into t5(id,dt1,dt2) values(2,'1987-04-11 17:30:00','1987-04-11 17:30:00');
insert into t5(id,dt1,dt2) values(1,'1987-04-11 16:30:00','1987-04-11 16:30:00'); 
insert into t5(id,dt1,dt2) values(2,'1987-04-11 15:30:00','1987-04-11 15:30:00');

select * from t5;
+------+---------------------+---------------------+
| id   | dt1                 | dt2                 |
+------+---------------------+---------------------+
|    1 | 1987-04-11 18:30:00 | 1987-04-12 02:30:00 |
|    2 | 1987-04-11 17:30:00 | 1987-04-12 01:30:00 |
|    1 | 1987-04-11 16:30:00 | 1987-04-12 00:30:00 |
|    2 | 1987-04-11 15:30:00 | 1987-04-11 23:30:00 |
+------+---------------------+---------------------+

set time_zone='Asia/Shanghai';
select * from t5;
+------+---------------------+---------------------+
| id   | dt1                 | dt2                 |
+------+---------------------+---------------------+
|    1 | 1987-04-11 18:30:00 | 1987-04-12 03:30:00 |
|    2 | 1987-04-11 17:30:00 | 1987-04-12 02:30:00 |
|    1 | 1987-04-11 16:30:00 | 1987-04-12 01:30:00 |
|    2 | 1987-04-11 15:30:00 | 1987-04-11 23:30:00 |
+------+---------------------+---------------------+

Suggested fix:
adjust start time to 2:00,not 0:00

same as jre:
https://www.oracle.com/java/technologies/tzdata-versions.html

tzdata2018f
2018/10/18

HI Mr. lee,

Thank you for your bug report.

We have analysed all documentation related to the China's DST and concluded that you are correct.

Verified as reported.

We have looked at this, and our guess is that you have loaded timezone info version 2018e or older, as this bug was fixed in 2018f:

https://github.com/eggert/tz/commit/937286d45c0f89132f0bf2d92fc27c36ae909cd6

MySQL has no initial knowledge of timezone data unless it has been loaded into the database:

https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

If you are on a Linux system, you can check the version of the timezone information in /usr/share/zoneinfo/tzdata.zi (first line in the file).

hi,Erlend Dahl

Just like  you said，my tzdata version is 2018e, since i upgrade to new version ,the DST is correct 

thx

Mr. Lee,

You are truly welcome.