Search code examples
mysqllaravellaravel-6.2

1292 Incorrect datetime value for column 'updated_at'

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?

Solution

  • 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.