Bug #85641 MySQL is not consequent in handling non existing time between 2 and 3 o'clock
Submitted: 27 Mar 2017 9:17 Modified: 27 Mar 2017 15:48
Reporter: Tibor Nagy Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: datetime summertime error

[27 Mar 2017 9:17] Tibor Nagy
Description:
If we try to insert a datetime value into the database, which is in the period of 2 and 3 o'clock AM at the date of changing to summertime in timezone CET, that we get an error, stating:

ERROR 1292 (22007): Incorrect datetime value: '2017-03-26 02:15:00' for column 't' at row 1

We could accept it, because this time doesn't exist in the current locale, the time is switched to 3 o'clock at 2 o'clock, because of the daylight saving time switch. But I have to say, that in MySQL version 5.5 the time was silently moved to 3 o'clock instead of reporting an error in such case. I would be glad, if I could control this behavior with a configuration.

However the big problem is, that MySQL gives back incorrect datetime values, using the operator "interval". To be consequent it should return the correct value. See sql command in "how to repeat". The select command should return "2017-03-26 03:15:00" and the UPDATE command should update teh record without error to "2017-03-26 03:15:00".

How to repeat:
mysql> create table a(t timestamp);
mysql> insert into a values ('2017-03-26 02:15:00');
ERROR 1292 (22007): Incorrect datetime value: '2017-03-26 02:15:00' for column 't' at row 1
mysql> insert into a values ('2017-03-26 01:15:00');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT t + interval 3600 second from a;
+--------------------------+
| t + interval 3600 second |
+--------------------------+
| 2017-03-26 02:15:00      |
+--------------------------+
1 row in set (0.00 sec)

mysql> UPDATE a set t =  t + interval 3600 second;
ERROR 1292 (22007): Incorrect datetime value: '2017-03-26 02:15:00' for column 't' at row 1

Suggested fix:
The select command should return "2017-03-26 03:15:00" and the UPDATE command should update teh record without error to "2017-03-26 03:15:00".
[27 Mar 2017 15:48] Sinisa Milivojevic
I have repeated your test case easily, as I live in the country with summer-time correction:

-------------------------------------------

mysql> create table aaa (t datetime);
Query OK, 0 rows affected (0.97 sec)

mysql> insert into aaa values ('2017-03-26 01:15:00');
Query OK, 1 row affected (0.07 sec)

mysql> SELECT t + interval 3600 second from aaa;
+--------------------------+
| t + interval 3600 second |
+--------------------------+
| 2017-03-26 02:15:00      |
+--------------------------+
1 row in set (0.13 sec)

mysql> UPDATE aaa set t =  t + interval 3600 second;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT t FROM aaa;
+---------------------+
| t                   |
+---------------------+
| 2017-03-26 02:15:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> drop table aaa;
Query OK, 0 rows affected (0.06 sec)

-------------------------------------------

I also agree with you that this is a bug, so I am verifying it.