Today I Learned MySQL Dates Are DST-Aware

2024-01-22-today-i-learned-mysql-dates-are-dst-aware.md

TIL MySQL is DST-aware and prevents storage of invalid times. This happened when running a database seeder on my local machine, time zone America/Chicago. A randomized date in 1983, 1983-04-24 02:22:57, caused the following error:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1983-04-24 02:22:57'

After some digging around, I realized 1983's Daylight Savings Time started on April 24th at 2 AM, where 2 AM would be skipped altogether, jumping to 3 AM. This meant any time zone-aware time between 2 and 3 AM was technically invalid. And sure enough, we can confirm this with the following:

CREATE TABLE test(created_at TIMESTAMP);

INSERT INTO test(created_at) VALUES ('1984-04-29 02:00:00'); -- ❌ Nope
INSERT INTO test(created_at) VALUES ('1984-04-29 01:59:59'); -- ✅ Yep
INSERT INTO test(created_at) VALUES ('1984-04-29 02:59:59'); -- ❌ Nope
INSERT INTO test(created_at) VALUES ('1984-04-29 03:00:00'); -- ✅ Yep

The same is true for every other time period, including 2024's DST, which starts on March 10, 2024, at 2 AM. The workaround, of course, is to set your database to a time zone that doesn't observe DST, like UTC. For now, on my local machine, I'll just re-run my seeder for this one-in-a-million problem. If it troubles me enough, I'll figure out how to properly configure MySQL's time zone setting.

P.S.: we should all be using "time zone", not "timezone", and I will die on this hill.

N.B. this only applies to TIMESTAMP columns and not DATETIME columns, as only TIMESTAMP columns are real UTC timestamps.

Likely something to do with the fact that:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
https://dev.mysql.com/doc/refman/8.4/en/datetime.html