Emil Forslund Emil Forslund - 1 month ago 8
MySQL Question

MySQL gives incorrect DateTime Value for some dates but not others

I have a table that looks like this in MySQL:

mysql> describe sale;
+-------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+-------------------+-----------------------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| salesperson | int(11) | NO | MUL | NULL | |
| customer | int(11) | NO | MUL | NULL | |
| product | int(11) | NO | MUL | NULL | |
| count | int(11) | NO | | NULL | |
+-------------+------------+------+-----+-------------------+-----------------------------+


Now if a run this statement, I get an ERROR 1292:

mysql> insert into `sale` values (25860,'2006-04-02 02:30:50',1,25,2,21);
ERROR 1292 (22007): Incorrect datetime value: '2006-04-02 02:30:50' for column 'timestamp' at row 1


However, if I just change the date by one day, the statement works:

mysql> insert into `sale` values (25860,'2006-04-03 02:30:50',1,25,2,21);
Query OK, 1 row affected (0.00 sec)


What is the magical thing about the second of April 2006? I can't detect any problems with the format specified. I also tried retyping the statement to make sure it was not caused by invisible characters.

Answer

Daylight Saving Time.

On 2006-04-02 at 2am the clock jumped to 3am. So there was no 02:30:50 enter image description here

Comments