SQLSTATE [22007]:无效的日期时间格式:1292错误的日期时间值:'2008-03-30 02:56:12' [英] SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2008-03-30 02:56:12'

问题描述

系统 本地PHP 5.6.19 使用Laravel 5.0制作的应用，使用fzaninotto/Faker作为播种机 Mysql远程

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

远程MySQL 带有MariaDB 10.1.13-MariaDB-1〜jessie的Debian 8

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

正在运行的播种机

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, [e-mail], 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))

..日期时间为2008-03-30 02:56:12

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

在出现错误之前使用播种器创建的最近3个用户的示例:

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','[e-mail]','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','[e-mail]','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','[e-mail]','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','[e-mail]','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');

阅读一些信息，例如 MYSQL错误的DATETIME格式，并为此问题验证mysql变量.

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)

推荐答案

问题很简单.这是有关夏令时"的问题

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

通过查询下表中的西班牙夏令时，以及该日期确实是当天27/03/2011，02:00 PM.因此，任何日期都是包含时间的一天，因为时钟将从02:00向前移动到02:59，所以它将是一个失败，因为时间是从02​​:00向前移动到02:59.

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.

这篇关于SQLSTATE [22007]:无效的日期时间格式:1292错误的日期时间值:'2008-03-30 02:56:12'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！