SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2008-03-30 02:56:12'

Question

System Local PHP 5.6.19 App made with Laravel 5.0, using fzaninotto/Faker as seeder Mysql remote

Remote MySQL Debian 8 with MariaDB 10.1.13-MariaDB-1~jessie

Running seeder

php artisan migrate:reset; php artisan migrate; php artisan db:seed

After seeder add an indeterminate number of users (different each time the reproduction of the error) get error:

[Illuminate\Database\QueryException]
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2008-03-30 02:56:12' for column 'created_at' at row 1 (S
  QL: insert into `users` (`type`, `name`, `email`, `nick`, `password`, `vat`, `address`, `zipcode`, `city`, `state`, `country`, `cr
  eated_at`, `updated_at`) values (customer, Adrian Santos, [email protected], rzavala12, y$Veqd2mkgRHtl6VKtYBV0yey77tVLo34.6THXqhtGRwg
  /Ea73/FhlK, 02058805Y, Plaça Abril, 85, 96º D, 44824, Vargas de San Pedro, 57431, San Roig, Huesca, ES, 2008-03-30 02:56:12, 2008-
  03-30 02:56:12))

Well.. date time it's 2008-03-30 02:56:12

A example of latest 3 users created with the seeder before get error:

INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15053,'buyer','[email protected]','celia657','$2y$10$P4SOJOAQHsC8W7ctJ5v1F.8loccYzjAW95ILwWZ1RzObNs01hKjr2','Bruno Aguado','71514876X','Paseo Corral, 1, Entre suelo 0º, 63362, La Saiz','58061','Navarrete de Ulla','Cádiz','ES',NULL,NULL,'2012-09-13 06:01:51','2012-09-13 06:01:51');
INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15052,'buyer','[email protected]','cristian126','$2y$10$fe2Hdh4p0bttNfo9uzwSe.L5OOzPwsm5p8g5.YhW5PAoxKcSObDta','Enrique Rivero','06604287K','Ronda Saul, 321, 8º C, 76264, San Álvarez de Arriba','11402','El Roybal Baja','Zamora','ES',NULL,NULL,'2007-11-14 16:39:41','2007-11-14 16:39:41');
INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15051,'buyer','[email protected]','aalmanza21','$2y$10$EA.NhWHuT8.nGoZjD18hqOKatHH3MbboY2DjZywlceH8K60spAe5m','Miriam Riojas','24055470T','Calle Soriano, 0, 75º A, 29744, L\' Montenegro','12236','As Martínez','Illes Balears','ES',NULL,NULL,'2013-03-25 23:15:50','2013-03-25 23:15:50');
INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15050,'buyer','[email protected]','mateo26','$2y$10$6.lFRsqEjXb1kdZOhAX7Cu1Xecgu1ZvAkhaADKcfSpEi6ODQM9nRK','Alonso Núñez','50161945A','Camino Ainara, 78, 65º A, 17155, L\' Ayala del Pozo','47226','Román de las Torres','Las Palmas','ES',NULL,NULL,'2016-05-12 11:00:01','2016-05-12 11:00:01');

Read some information, such MYSQL incorrect DATETIME format, How to make sql-mode=“NO_ENGINE_SUBSTITUTION” permanent in MySQL my.cnf and verify mysql variables for this question.

MariaDB [(none)]> select @@GLOBAL.sql_mode;
--------------
select @@GLOBAL.sql_mode
--------------

+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
MariaDB [(none)]> select @@SESSION.sql_mode;
--------------
select @@SESSION.sql_mode
--------------

+------------------------+
| @@SESSION.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

Solution

The question is simple. It is a problem concerning the "Daylight Saving Time"

By consulting the table for Daylight Saving Time Spain, and that date is the day indeed 27/03/2011 at 02:00 PM. Therefore, any date is a day that contains a time, between 02:00 and 02:59 will be a failure, because the clock is moved forward from 02:00 to 02:59.

Comments

[wchiquito]

Columns (created_at and updated_at) are of type TIMESTAMP? With the data type DATETIME this does not happen.

[abkrim]

@wchiquito that's it's not question. Question it's USE DATETIME.Apreciate your comments. Also if read carefully, column has dattime format. Erro show. And yes, happen. Read my solution, and try same insert., for reproduce problem.

[wchiquito]

I can't reproduce the problem with the data type DATETIME. With TIMESTAMP expected error occurs.

[Rick James]

TIMESTAMP is stored as UTC. Converting from "02:56" is impossible for one hour per year, hence TIMESTAMP appears to fail. In reality, it is "bad input" because "02:56" does not exist, just as "Feb 29" does not exist in most years. Also, leap seconds may cause trouble.

[JamieHoward]

I had trouble fully understanding the provided answer at first, but @abkrim is correct. As a summary, Faker is not taking daylight savings time into account, so it may provide times that may not exist. For example, in 2016, in USA, DST started on March 13, so at 2am, the clocks moved to 3am. Thus, "2016-03-13 02:01:00" through "2016-03-13 02:59:00" are invalid timestamps because the times never existed.