SQLSTATE [22007]:无效的日期时间格式:1292错误的日期时间值:'2019-03-31 01:52:25' [英] SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-03-31 01:52:25'

问题描述

我目前从多个生产数据库中收到很多错误，我坚持说:

I'm currently receiving lot's of errors from more than one production database that I maintain saying:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-03-31 01:49:45' for column 'created_at' at row 1 

这是我的桌子:

+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| log        | varchar(255) | YES  |     | NULL              |                |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP |                |
| updated_at | datetime     | YES  |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+

MySQL版本:

mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

有人可以帮助我了解发生了什么事吗?

Can someone help me understand what's happening?

更新:
我不知道发生了什么，但是我收到了来自错误监视器的大量通知，现在一切都恢复了正常，而我这一边的代码没有任何更改.

UPDATE:
I've no clue of what happened but I was receiving lot's of notifications from my error monitors and now all is back to normal without any change in the code from my side.

错误始于:2019-03-31 01:00:08
并结束于:2019-03-31 01:59:03

The errors started at: 2019-03-31 01:00:08
And ended at: 2019-03-31 01:59:03

更新2:
好的，所以我来自葡萄牙，今天计划将国家时间改成+1小时，但我完全忘记了...
我怀疑这些错误是由于服务器或MySQL安装与新时间同步而导致的.

UPDATE 2:
Ok so I'm from Portugal and today it was scheduled a national time change for +1 hour that I totally forgot...
I suspect the errors were due to my servers or my MySQL installation lagging in syncing with the new time.

如果其他任何人遇到相同的问题，我将保留这个问题

I'll leave this question up in case anyone else encounters the same problem

推荐答案

这是因为从当天开始实行夏令时，所以凌晨1点到凌晨2点之间的所有时间都是无效的.您会发现问题实际上始于"01:00:00"，始于"01:59:59":

It's because of daylight savings time starting that day, so all times between 1am and 2am are invalid. You'll find the problem actually starts at '01:00:00' and ends at '01:59:59':

create table t (created_at timestamp null default current_timestamp);
insert into t values('2019-03-31 00:59:59');
insert into t values('2019-03-31 01:00:00');

第1行"created_at"列的日期时间值不正确:"2019-03-31 01:00:00"

Incorrect datetime value: '2019-03-31 01:00:00' for column 'created_at' at row 1

insert into t values('2019-03-31 01:59:59');

第1行"created_at"列的日期时间值不正确:"2019-03-31 01:59:59"

Incorrect datetime value: '2019-03-31 01:59:59' for column 'created_at' at row 1

insert into t values('2019-03-31 02:00:00');
select * from t

输出:

created_at
2019-03-31 00:59:59
2019-03-31 02:00:00

将系统时区更改为没有夏时制的时区将解决此问题.

Changing your system timezone to one that does not have daylight savings time will solve the problem.

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