1292 Incorrect datetime value for column 'updated_at'

Question

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']);
});

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))

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')

I'm using MySQL 5.7.

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')

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

This is not a reproducible problem, and there is nothing at all special about 2 AM to MySQL. — Tim Biegeleisen, Mar 08 '20 at 02:49
I would love to believe that. I've never seen this happen before in my life but I can watch it happening. I could even record a screenshare running the SQL raw to demonstrate. — eComEvo, Mar 08 '20 at 02:50
Just a hunch: In your country, does daylight saving time start today maybe, and clocks go from 1:59am to 3:00am? This could explain the error, since 2am simply _wouldn't exist_ on that day. — CherryDT, Mar 08 '20 at 02:56
@CherryDT That is only way I could explain this without assuming I'm in an episode of the Twilight Zone. Daylight Savings is (also nonsensically) upon us. — eComEvo, Mar 08 '20 at 02:59
This is an annoying consequence of using `DATETIME` which uses local timezone. (I prefer `TIMESTAMP` for that reason.) Also, think about what happens when DST ends - there are _two_ 2am-s, but in SQL you cannot distinguish between 2A:00 and 2B:00, so be ready for that! — CherryDT, Mar 08 '20 at 03:01

CherryDT · Accepted Answer · 2020-03-08T03:09:18.170

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

Therefore, 2am does not exist today.

March 8th, 2020 - Daylight Saving Time Starts

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.

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.

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.

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 Incorrect datetime value for column 'updated_at'

1 Answers1

Linked