Bug #39505 ERROR 1292 (22007): Incorrect datetime value

• Submitted: 17 Sep 2008 21:55
• Modified: 17 Oct 2008 23:18
• Reporter: Eugene Toropov
• Status: No Feedback
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.1.28
• OS: Windows
• CPU Architecture: Any

[17 Sep 2008 21:55] Eugene Toropov

Description:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test (t timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test set t = '2008-03-30 02:53:49';
ERROR 1292 (22007): Incorrect datetime value: '2008-03-30 02:53:49' for column 't' at row 1
mysql>

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test (t timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test set t = '2008-03-30 02:53:49';
ERROR 1292 (22007): Incorrect datetime value: '2008-03-30 02:53:49' for column 't' at row 1
mysql>

[17 Sep 2008 23:18] MySQL Verification Team

Thank you for the bug report.

Please see:

http://bugs.mysql.com/bug.php?id=34214 about the aylight savings time.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use test
Database changed
mysql 5.1 >create table test (t timestamp);
Query OK, 0 rows affected (0.16 sec)

mysql 5.1 >insert into test set t = '2008-03-30 02:53:49';
Query OK, 1 row affected (0.08 sec)

[18 Oct 2008 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[17 Mar 2017 17:18] Brady Brown

I can reproduce this on MySQL Community Server (GPL) 5.7.17:

mysql> create table wtf (dt timestamp);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into wtf values('2016-03-13 02:33:21');
ERROR 1292 (22007): Incorrect datetime value: '2016-03-13 02:33:21' for column 'dt' at row 1

Modifying the value of seconds or minutes to any other valid value still results in the error.

Modifying the value of year, month, day, or hour to any other value makes the error go away.

Changing the column type from TIMESTAMP to DATETIME makes the error go away.