| 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: | |
| 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 15:48]
MySQL Verification Team
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.
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".