Bug #42737 Inserting a valid datetime into a timestamp column causes warning
Submitted: 10 Feb 2009 19:04 Modified: 10 Feb 2009 21:07
Reporter: Boris Ioffe Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.67, 5.1.30 OS:Linux (RHEL 5 and FreeBSD)
Assigned to: CPU Architecture:Any
Tags: Inserting a valid datetime into a timestamp column causes warning

[10 Feb 2009 19:04] Boris Ioffe 
Description:
subj,
Always reproducible and extremely irritating  

How to repeat:
CREATE TABLE t (ts1 TIMESTAMP NULL DEFAULT NULL );
insert into t (ts1) values ("2009-03-08 02:29:45");

Query OK, 1 row affected, 1 warning (0.00 sec) 

Works fine with 
insert into t (ts1) values ("2009-03-08 01:29:45");
insert into t (ts1) values ("2009-03-08 03:29:45");

select * from t;
+---------------------+
| ts1                 |
+---------------------+
| 2009-03-08 01:29:45 |
| 2009-03-08 03:00:00 | <-- rounded value with lost data 
| 2009-03-08 03:29:45 |
+---------------------+
[10 Feb 2009 19:10] Sveta Smirnova 
Thank you for the report.

Please provide output of  show variables like '%time_zone%';
[10 Feb 2009 19:23] Boris Ioffe 
mysql> show variables like '%time_zone%'; 
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | EST    | 
| time_zone        | SYSTEM | 
+------------------+--------+
2 rows in set (0.06 sec)
[10 Feb 2009 19:35] Boris Ioffe 
and redhat machine is 

 show variables like '%time_zone%'; 
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    | 
| time_zone        | SYSTEM | 
+------------------+--------+
2 rows in set (0.00 sec)
[10 Feb 2009 20:06] Boris Ioffe 
WORKAROUND: 
   SET SESSION time_zone=UTC; // fixes the behavior, but doesn't explain why it corrupts the data  in any other TZ.
[10 Feb 2009 21:07] Sveta Smirnova 
Thank you for the feedback.

Both CST and EST time zones are affected by daylight saving time. See http://www.timeanddate.com/time/aboutdst.html for details. So date "2009-03-08 01:29:45" simple does not exists and converted to nearest valid date "2009-03-08 03:00:00"