MySQL TIMESTAMP column rejecting specific datetime format with error 1292

Other Questions MySQL
1

I’m running into a weird issue with MySQL timestamps and I can’t figure out what’s going on. I have a basic table setup like this:

CREATE TABLE `events` (
  `event_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When I try to insert a specific datetime value, it gets rejected:

insert into events values('2016-03-27 02:00:00');

This throws an error: ERROR 1292 (22007): Incorrect datetime value: '2016-03-27 02:00:00' for column 'event_time' at row 1

But if I change the time to one hour earlier, it works perfectly fine:

insert into events values('2016-03-27 01:00:00');

This one goes through without any problems. Both timestamps use the exact same format, so I’m really confused about why one works and the other doesn’t. Is there some kind of restriction I’m missing? Could this be related to daylight saving time or timezone settings? I’m using MariaDB 10.2.9 on Linux. Any help would be really appreciated!

2

Been there! Same headache migrating old system data. The worst part? The error doesn’t even mention DST - you’re just staring at what looks like a valid timestamp wondering what’s broken. First thing I’d check is SELECT @@global.time_zone, @@session.time_zone to see what timezone rules MySQL’s using. If you need those exact timestamps, switch your session to UTC before inserting, then flip back after. But honestly? After hitting this wall on multiple projects, I just store everything in UTC now. Way less debugging later.

3

Yeah, that’s daylight saving time messing with you. On 2016-03-27, clocks jumped from 1:59:59 straight to 3:00:00 - so 2:00:00 never actually existed. MySQL knows this and won’t accept timestamps from that missing hour. You can fix it by shifting your timestamps out of that gap or tweaking MySQL’s timezone settings. Run SELECT @@time_zone to check what you’re working with. If you’re dealing with DST transitions a lot, just store everything in UTC.

4

yup, totally a dst thing! on march 27, 2016, the clocks moved forward, so 2:00 am never really existed, it just skipped to 3:00. mySQL is just being picky about those invalid times during the spring forward.

5

Yes, this is indeed a daylight saving time issue. On March 27, 2016, the transition in many time zones meant that the clocks jumped forward, causing 2:00 AM to not exist at all. MySQL adheres to these timezone rules, and thus rejects any timestamp that falls within that non-existent hour. To resolve this, you should either adjust your inserts to valid timestamps or consider storing all timestamps in UTC to eliminate issues related to DST in the future.

6

This DST mess is exactly why I automate all my database ops now.

Everyone’s right - March 27, 2016 was spring forward, so 2 AM never existed. But fixing these timestamps manually? Total pain, especially with bulk imports or live feeds.

Hit the same issue last year with event logs from different timezones. Instead of writing custom validation or constantly converting to UTC by hand, I built an automated workflow that handles timestamp validation and conversion before it touches the database.

It catches invalid DST timestamps, auto-adjusts them to valid times (shifts forward an hour or converts to UTC), and logs changes for audits. Takes 5 minutes to set up, saves hours debugging weird MySQL errors.

You can build something similar to monitor data inputs and handle timezone quirks automatically: https://latenode.com

7

DST strikes again! MariaDB has a sql_mode setting for this - try SET sql_mode = 'ALLOW_INVALID_DATES' before your insert. But honestly, switching to UTC timestamps is a much cleaner long-term fix.