"#1292 - Incorrect datetime value" when time is within one hour

Question

I'm running into a bizarre error with a timestamp column in a MySQL DB. When inserting the date/time value, it fails with a #1292 error if the time is between 02:00:00 and 02:59:59. Here is a sample setup:

CREATE TABLE `test_timestamp` (
`id` int NOT NULL AUTO_INCREMENT,
`test_date` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

INSERT INTO test_timestamp (test_date)
VALUES ('2024-03-10 02:20:45')

Result: #1292 - Incorrect datetime value: '2024-03-10 02:20:45' for column 'test_date' at row 1

I thought this might be a timezone issue, but it doesn't make sense that it would only impact this one hour range. For reference, my system TZ is EDT and time zone is SYSTEM and we're running MySQL 8.0.36.

It's very reproducible -- I ran into it with one table and have since created several test tables to replicate. Any timestamp field will not accept a value within that one hour range.

Answer 1

The US treats 2024-03-10 as the date for Daylight Savings Time adjustment. At 02:00, clocks advance by one hour. So effectively there is no hour between 02:00 and 03:00 on that date.

https://www.timeanddate.com/time/change/usa?year=2024

I hope someday we can stop doing this, because it makes software developers' work more difficult. There is a bill this year (2024) in the legislatures of California, Oregon, and Washington states to end this twice-annual adjustment, and make Standard Time permanent.