1292列'updated_at'的日期​​时间值不正确 [英] 1292 Incorrect datetime value for column 'updated_at'

问题描述

我已经在Laravel中创建了带有标准datetime列的表:

I've created a table in Laravel with standard datetime columns:

Schema::create('lists', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('name');
    $table->string('ref');
    $table->string('provider');
    $table->timestamps();
    $table->softDeletes();

    $table->unique(['provider', 'ref']);
});

当我尝试用Eloquent创建简单的记录时:

When I try to do a simple record creation with Eloquent:

List::updateOrCreate([
    'provider' => 'test',
    'ref'      => 'S4d3g'
], [
    'name' => 'Plan'
]);

我收到此消息(这是原始控制台输出，因此请忽略引号的缺少):

I am given this message (which is a raw console output, so ignore the lack of quotes):

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2020-03-08 02:25:07' for column 'updated_at' at row 1 (SQL: insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values (test, S4d3g, Plan, 2020-03-08 02:25:07, 2020-03-08 02:25:07))

作为原始SQL在数据库上手动运行查询也不起作用:

Running the query manually on the database as raw SQL also doesn't work:

insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values ('test', 'S4d3g', 'Plan', '2020-03-08 02:25:07', '2020-03-08 02:25:07')

我正在使用MySQL 5.7.

I'm using MySQL 5.7.

莫名其妙的是，如果我将日期更改为凌晨2点以外的任何其他日期，那么它会起作用:

Inexplicably, if I change the date to anything other than 2 AM, it works:

insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values ('test', 'S4d3g', 'Plan', '2020-03-08 01:25:07', '2020-03-08 01:25:07')
insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values ('test', 'S4d3g', 'Plan', '2020-03-08 03:25:07', '2020-03-08 03:25:07')

是什么原因导致时间戳上凌晨2点不喜欢这种奇怪的MySQL级别?

What could be causing this bizarre MySQL level dislike of 2 AM on the timestamp?

推荐答案

您似乎在美国，所在的时区为夏令时刚刚开始.

It appears you are in the US, in a timezone where daylight saving time just started.

因此，今天凌晨2点不存在.

Therefore, 2am does not exist today.

2020年3月8日-夏令时开始

March 8th, 2020 - Daylight Saving Time Starts

即将达到当地标准时间
2020年3月8日，星期日，02:00:00时钟向前调整1个小时到
而不是2020年3月8日，星期日，当地夏令时.

When local standard time is about to reach
Sunday, March 8th, 2020, 02:00:00 clocks are turned forward 1 hour to
Sunday, March 8th, 2020, 03:00:00 local daylight time instead.

到2020年3月8日，日出和日落比前一天晚约1个小时. 晚上会有更多的灯光.

Sunrise and sunset will be about 1 hour later on March 8th, 2020 than the day before. There will be more light in the evening.

MySQL中的

DATETIME使用本地时间(可以以多种方式设置时区)，在您的情况下，它可能是您的 当地时间，这就是您遇到此问题的原因.如果您实际上是指 UTC ，则必须先使用SET time_zone = "+00:00"或通过设置正确的

DATETIME in MySQL uses a local time (the timezone can be set in a multitude of ways) and in your case it's probably your local time, which is why you run into this issue. If you actually meant UTC, you'd have to set the timezone to UTC first using SET time_zone = "+00:00" or by setting the correct global configuration.

由于您的PHP库正在生成此(无效)时间，因此我假设您在PHP和MySQL使用的时区之间不匹配.

Since your PHP library is generating this (invalid) time, I assume you have a mismatch between the timezones used by PHP and by MySQL.

这篇关于1292列'updated_at'的日期​​时间值不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！