Laravel SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-03-10 02:00:39' for column 'updated_at' (daylight savings?)

Question

I'm working on a Laravel application with a queue worker, and it just started failing and throwing a constant stream of exceptions to the log. Here is one of them:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-03-10 02:00:39' for column 'updated_at' at row 1 (SQL: update `videos` set `updated_at` = 2019-03-10 02:00:39 where `id` = 30860) 

A few things to note:

• The timestamp appears to be the correct format
• The column is a MySQL TIMESTAMP type (nullable), using InnoDB
• The updated_at field is managed by Laravel defaults, with nothing custom happening for the date formatting
• I'm not using any date mutators, or anything that would be changing the value before save
• This code has been running for weeks without any issue
• No code has changed recently
• I did a git checkout . and composer install to make sure none of the library files had changed or been corrupted somehow (no change)
• The error first appeared at 2019-03-10 02:00:39 (UTC), which is 8pm local (Central) time

Daylight saving time 2019 in Minnesota will begin at 2:00 AM on Sunday, March 10

-- Google

Everything I can find on google is related to somebody using a blatantly wrong time format, so that hasn't helped me figure it out.

I feel like there is too much of a coincidence between the errors starting at 02:00 UTC on the same day when daylight savings is supposed to begin at 02:00 US Central Time, but I don't have a clue why it would be throwing this error when timestamps are managed by the framework and converted to UTC before saving (and UTC doesn't change for daylight savings).

MySQL 5.5.45
Laravel 5.7.28
nesbot/carbon 1.36.2
PHP 7.2.7

config/database.php:

'mysql' => [
    
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

What could be causing the date value to be recognized as invalid?

Edit: I've determined this is definitely a daylight savings error, as clocks skip forward 1 hour and times between 02:00 and 03:00 are invalid in US Central Time. What I don't understand is why a TIMESTAMP field on an application set to UTC would be throwing an error? How can UTC be affected by daylight savings, and what can I do to prevent this when the timestamp is managed by the framework (Laravel+Carbon)?

Answer 1

I figured out that the problem was due to my MySQL server's time_zone setting being set to SYSTEM (and my system is US Central). Laravel is providing timestamps that were already converted to UTC, but my database is interpreting them as US Central due to the time_zone setting. The times are actually being converted again internally by MySQL to a "real" UTC unix timestamp representation (which will be incorrect because it's offset by the time zone), even though they appear to be UTC already in every query because they also get converted back to US Central again for reading (I know right).

Because of this, at 20:00:39 (8:00 PM) local time my Laravel UTC timestamps are 02:00:39. MySQL interprets these times as US Central time, and because the time is between 02:00 and 03:00 (which is when clocks skip forward for US Central), the time is invalid.

The best solution for a Laravel application is to force every database connection to use a +00:00 timezone (or whatever you have set as the application timezone in config/app.php) so there will not be a secondary conversion happening. This can be done in config/database.php:

'mysql' => [
    // ...

    'timezone'  => '+00:00'
],

This way you are not at the mercy of your database server if it has a configured timezone that is different from your Laravel application. The other option is to change the database's time_zone setting, but then you still risk the bug recurring if you ever change hosts or need to rebuild the server for any reason (and don't configure the timezone correctly again), or affecting other databases on the server.

Important note: Since all of the previous timestamps were being offset internally by MySQL from the configured time zone to UTC unix timestamps (which, again, were wrong because the records were already UTC) it might be necessary to run a data migration to correct the old timestamps. I haven't investigated further because for my application it doesn't matter if the old timestamps were wrong by a few hours.