MySQL 5.6.19 TIMESTAMP Can't accept many correct values

Question

I'm hitting a really strange problem and don't understand this.

CREATE TABLE test (time TIMESTAMP NOT NULL DEFAULT NOW()) ENGINE=INNODB;

mysql> insert into test (time) values("2011-03-13 01:08:04");
Query OK, 1 row affected (0.00 sec)

This is good. Now, change the time by only 1 hour:

mysql> insert into test (time) values("2011-03-13 02:08:04");
ERROR 1292 (22007): Incorrect datetime value: '2011-03-13 02:08:04' for column 'time' at row 1

What is going on here? I'm randomly inserting datetime values and find many values can't be inserted: "2011-03-13 02:08:04", "2010-03-14 02:04:05", "2009-03-08 02:24:52", "2009-03-08 02:48:27", "2011-03-13 02:06:01", "2005-04-03 02:00:44"...

Changing the hour by 1 or the year by 1 resolves the problem, but of course is not remotely a real fix.

Answer 1

Those datetimes look a lot like invalid values for a US timezone that observes daylight saving time adjustments.

On a Sunday morning in March, daylight saving time clocks "spring forward" one hour, effectively skipping the hour between 2AM and 3AM. The hour between 2AM and 3AM doesn't exist, so values that specify that hour aren't "correct" values.

The behavior you observe is the expected behavior.

Note that those values would be valid in UTC 'time_zone=+0:00', or in a timezone that doesn't observe daylight savings time.

(Note also that converse issue happens in the fall, when clocks "fall back" one hour; then there are two separate hours, between 2AM and 3AM, that have the same encoded value, but differ in the timezone. 02:30 CST vs 02:30 CDT)

Answer 2

Daylight savings.

In 2011, daylight savings time began at 2AM on March 13th. Therefore, anytime between 2AM and 3AM is invalid.

MySQL produces a warning when not in strict mode. If either STRICT_ALL_TABLES or STRICT_TRANS_TABLES are set then you could unset all sql modes by issuing, though it should be noted this is not recommended.

SET @@sql_mode='';    
insert into test (time) values("2011-03-13 02:08:04");

You'll still get a warning about an invalid timestamp, and the value 2011-03-13 03:00:00 will be inserted into your table.