MySQL Bugs: #34135: Each year April has a one hour block that won't accept a valid timestamp

Bug #34135	Each year April has a one hour block that won't accept a valid timestamp
Submitted:	29 Jan 2008 14:51	Modified:	31 Jan 2008 16:32
Reporter:	Christopher Shaw	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Windows (XP, server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	april, timestamp

Description:
MySQL has an invalid date error when trying to enter a valid timestamp for a one hour block each April on Windows systems.  When tested on Linux, it doens't have the same issue.  The dates that were problematic are listed below:
1970-04-05 02:02:02
1971-04-04 02:02:02
1972-04-02 02:02:02
1973-04-01 02:02:02
1974-04-07 02:02:02
1975-04-06 02:02:02
1976-04-04 02:02:02
1977-04-03 02:02:02
1978-04-02 02:02:02
1979-04-01 02:02:02
1980-04-06 02:02:02
1981-04-05 02:02:02
1982-04-04 02:02:02
1983-04-03 02:02:02
1984-04-01 02:02:02
1985-04-07 02:02:02
1986-04-06 02:02:02
1987-04-05 02:02:02
1988-04-03 02:02:02
1989-04-02 02:02:02
1990-04-01 02:02:02
1991-04-07 02:02:02
1992-04-05 02:02:02
1993-04-04 02:02:02
1994-04-03 02:02:02
1995-04-02 02:02:02
1996-04-07 02:02:02
1997-04-06 02:02:02
1998-04-05 02:02:02
1999-04-04 02:02:02
2000-04-02 02:02:02
2001-04-01 02:02:02
2002-04-07 02:02:02
2003-04-06 02:02:02
2004-04-04 02:02:02
2005-04-03 02:02:02
2006-04-02 02:02:02
2007-04-01 02:02:02
2008-04-06 02:02:02
2009-04-05 02:02:02
2010-04-04 02:02:02
2011-04-03 02:02:02
2012-04-01 02:02:02
2013-04-07 02:02:02
2014-04-06 02:02:02
2015-04-05 02:02:02
2016-04-03 02:02:02
2017-04-02 02:02:02
2018-04-01 02:02:02
2019-04-07 02:02:02
2020-04-05 02:02:02
2021-04-04 02:02:02
2022-04-03 02:02:02
2023-04-02 02:02:02
2024-04-07 02:02:02
2025-04-06 02:02:02
2026-04-05 02:02:02
2027-04-04 02:02:02
2028-04-02 02:02:02
2029-04-01 02:02:02
2032-04-04 02:02:02
2033-04-03 02:02:02
2034-04-02 02:02:02
2035-04-01 02:02:02
2036-04-06 02:02:02

How to repeat:
Create table with timestamp field, then try to insert any of the above values or values within the hour range of 0200 - 0300 for the day.

Thank you for the bug report. Which exactly version are you using?. I wasn't
able to repeat with a server 4 source days older. Please provide your my.ini
file and the create table, insert data sample. Thanks in advance.

Check the timezone on the two systems. This looks like a daylight savings time issue.

It's running everything on a laptop.  Does MySQL just use the system time or does it track it itself?

I'm in a class now, but I'll get the configuration file and other information later today.

My.ini

Attachment: my.ini (application/octet-stream, text), 9.03 KiB.

Version: 5.1.22

mysql> CREATE TABLE `date` (`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (1.48 sec)

mysql> INSERT INTO date (d) VALUES ('2007-04-01 02:02:02');
ERROR 1292 (22007): Incorrect datetime value: '2007-04-01 02:02:02' for column '
d' at row 1

mysql> show variables like '%zone%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| system_time_zone | Central Standard Time |
| time_zone        | SYSTEM                |
+------------------+-----------------------+
2 rows in set (0.69 sec)

My timezone is set to CST

There's no bug here. The datetime value 2007-04-01 02:02:02 is, indeed, incorrect, there was no such time in CST. Right after 2007-04-01 01:59:59 comes 2007-04-01 03:00:00, see, for example

 http://www.timeanddate.com/worldclock/meetingtime.html?day=1&month=4&year=2007&p2=155&iv=1...

Bug: http://bugs.mysql.com/bug.php?id=35815 was marked as duplicate of this
one.