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
– Tim Biegeleisen, Commented Mar 8, 2020 at 2: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
– eComEvo, Commented Mar 8, 2020 at 2: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
– CherryDT, Commented Mar 8, 2020 at 2: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
– eComEvo, Commented Mar 8, 2020 at 2: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
– CherryDT, Commented Mar 8, 2020 at 3:01

CherryDT · Accepted Answer · 2020-03-08 03:09:18Z

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.

Collectives™ on Stack Overflow

1292 Incorrect datetime value for column 'updated_at'

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related