CODEWITHSUNDEEP
mysql
David Portabella
David Portabella

Reputation: 12720

mysql 5.7 incorrect timestamp value '2016-03-27 02:01:01'

why 2016-03-27 02:01:01 is an invalid timestamp/datetime value?

mysql> CREATE TABLE table1(time1 TIMESTAMP);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO table1 VALUES('2016-03-27 01:01:01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table1 VALUES('2016-03-27 03:01:01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table1 VALUES('2016-03-27 02:01:01');
ERROR 1292 (22007): Incorrect datetime value: '2016-03-27 02:01:01' for column 'time1' at row 1

mysql> SELECT @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table1 VALUES('2016-03-27 02:01:01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql  Ver 14.14 Distrib 5.7.11, for osx10.10 (x86_64) using  EditLine wrapper

Upvotes: 1

Views: 1456

Answers (1)

Guido
Guido

Reputation: 926

Seems to me like a timezone issue. 2016-03-27 02:01:01 was the time when Daylight Savings Time became active in Europe. MYSQL is known to have problems with this – see MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?

Upvotes: 3

Related Questions