| Bug #64632 | Cannot insert date '2011-03-27 02:00:00' | ||
|---|---|---|---|
| Submitted: | 13 Mar 2012 10:10 | Modified: | 19 Mar 2012 10:19 |
| Reporter: | Sebastien Villanueva Thurmes | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.1.52 | OS: | Windows (Seven 32 bits and XP 64bits) |
| Assigned to: | CPU Architecture: | Any | |
[13 Mar 2012 11:56]
Peter Laursen
"Daylight saving time" problem! Are you sure that on '2011-03-26 02:00:00' there was not a switch between "Daylight saving time" and "non-Daylight saving time" for the tim3ezone whre the server is running? 2011-03-26 looks to me like the change of nonDST>DST for most European timezones. In that case after '2011-03-26 01:59:59' comes '2011-03-26 03:00:00'. The hour-leap is here. Solution:set system timezone to UTC or set MySQL timezone to UTC (or another timezome not using DST). Peter (Not a MySQL person)
[13 Mar 2012 12:04]
Valeriy Kravchuk
Looks like Peter is right. This is what I have:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.1.58-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table if not exists mytest( mytime TIMESTAMP);
Query OK, 0 rows affected (2.02 sec)
mysql> insert into mytest(mytime) values ('2011-03-26 02:00:00');
Query OK, 1 row affected (0.27 sec)
mysql> insert into mytest(mytime) values ('2011-03-27 02:00:00');
Query OK, 1 row affected (0.08 sec)
mysql> show variables like 'time%';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1331640171 |
+---------------+------------+
4 rows in set (0.00 sec)
[19 Mar 2012 10:19]
Sebastien Villanueva Thurmes
Yes, you are right! This date value in my timezone is not possible. Thanks! I have to find a kind of check before inserting.
[28 Jan 2015 16:17]
Dave Watts
If you want to avoid this 'bug' then you can change to UTC time, see this blog, restart mysql for effect http://nonparametrics.com/2011/03/02/timezone-fun-with-mysql-and-utc/ sudo /etc/init.d/mysql restart
Description: ERROR 1292 (22007): Incorrect datetime value: '2011-03-27 02:00:00' It is not possible to insert this simple date. Server version: 5.1.52-community-log MySQL Community Server How to repeat: Simply try this little script in mysql monitor. The first insert will success but the second fails. create table if not exists mytest( mytime TIMESTAMP); insert into mytest(mytime) values ('2011-03-26 02:00:00'); insert into mytest(mytime) values ('2011-03-27 02:00:00');