3

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.

1
  • Down with Daylight Savings!
    – Rick James
    Apr 29 at 0:59

1 Answer 1

7

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.

2
  • Wow. Didn't even think it might be related to daylight savings time, but that makes perfect sense. What a pain! Apr 23 at 15:53
  • 2
    @lcdservices And the question is... Where did you get a time and date which doesn't exist?
    – jcaron
    Apr 24 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.