Invalid datetime format caused by Daylight Saving Time
Just today, I encountered a strange error on my production server.
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2024-03-10 02:49:20' for column 'updated_at'
Looking at the SQL code, I couldn't identify anything unusual. It seemed like a straightforward update operation, updating the updated_at column with a datetime value.
The Impact of Daylight-Saving Time (DST)
After some investigation, I realised that the error occurred during the transition period of daylight-saving time, when the timezone for my servers in the USA changed today.
In the U.S., daylight saving time starts on the second Sunday in March and ends on the first Sunday in November, with the time changes taking place at 2:00 a.m. local time. With a mnemonic word play referring to seasons, clocks "spring forward, fall back"—that is, in springtime the clocks are moved forward from 2:00 a.m. to 3:00 a.m. and in fall they are moved back from 2:00 a.m. to 1:00 a.m.
Digging deeper, I found that my Laravel config/app.php timezone is set to UTC (Coordinated Universal Time), while my default MySQL timezone follows the SYSTEM time (US Central timezone).
To reproduce the error, I tried executing the following SQL with phpMyAdmin.
SET SESSION sql_mode = 'STRICT_ALL_TABLES';
UPDATE customers set updated_at = '2024-03-10 02:00:00' where id = 1;
-- #1292 - Incorrect datetime value: '2024-03-10 02:00:00' for column 'updated_at' at row 1
This error occurs because there is effectively no such hour in the locations that observe this time zone - the clock jumps from 2:00 AM to 3:00 AM, making any DATETIME records for this hour invalid.
We can further test this with other time zones that observe Daylight Savings Time. For example, the daylight change for Great Britain occurs at 1 AM on March 31st, so the following query will also result in an error:
SET time_zone = 'GB';
SET SESSION sql_mode = 'STRICT_ALL_TABLES';
UPDATE customers set updated_at = '2024-03-31 01:00:00' where id = 1;
Finding the Solution
The best solution is to change our MySQL default timezone to UTC or +00:00.
As UTC (Coordinated Universal Time) does not observe daylight saving time, it remains constant throughout the year and does not change with the transitions associated with daylight saving time.
Implementing the solution was straightforward in Laravel. We can modify the database configuration in config/database.php to set the timezone for MySQL connections to UTC.
Additionally, you need to ensure that existing timestamp values in the database were correctly migrated to UTC if necessary to maintain data integrity.
// config/database.php
'mysql' => [
// ...
'timezone' => '+00:00'
],
Reference