1

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
David Portabella
  • 12,390
  • 27
  • 101
  • 182

1 Answers1

3

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?

Community
  • 1
  • 1
Guido
  • 876
  • 5
  • 14
  • yes, that could explain it! however the most amazing thing is that i did an export of a mysql database (mysqldump), upgraded mysql to 5.7 and then i am having this (and other) problem with the import. so, is this is because of the daylight savings, how i get this value in the first place? i guess that this was not checked in the old database. – David Portabella Mar 31 '16 at 06:51