Bug #66240 incorrect datetime value trying to insert valid date
Submitted: 7 Aug 2012 9:57 Modified: 9 Aug 2012 9:04
Reporter: Oliver Abraham Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.25a OS:Windows (Win2k3)
Assigned to: CPU Architecture:Any
Tags: insert, timestamp

[7 Aug 2012 9:57] Oliver Abraham
Description:
trying to insert the value '2011-03-27 02:40:04' into a timestamp column results in the error message "incorrect datetime value".
Other date values work, i.e. 03-26 or 03-28 !

How to repeat:
create table test ( a timestamp )
insert into test values ( '2011-03-27 02:40:04' )
[7 Aug 2012 12:30] Peter Laursen
'daylight saving time' problem!

Does the timezone your server is running skip one hour between 2am and 3am on that date? Actually I get same error on MySQL 5.5.23 running CET timezone.
[7 Aug 2012 12:37] Peter Laursen
Actually I did a mistake: TIMESTAMPS are stored in UTC where 'daylight saving time' does not apply (unlike DATETIMES that are stored in client's time).  So it is rather the question if the client is running on a machine 'shifting' one hour at that time. That would make the conversion from client's time to UTC impossible.

Docs about same: http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Peter
(and I forgot to mention that I am not a MySQL/Oracle person).
[7 Aug 2012 12:50] Peter Laursen
Just pinning it out:

There is no such time as '2011-03-27 02:40:04' CET.  The time specification is invalid for that particular date and timezone. 

Not sure about UK, but I would be very surprised if UK does not have 'daylight saving time' from/to same dates as rest of Western Europe.
[9 Aug 2012 9:03] Oliver Abraham
Thanks, it seems to be the daylight savings problem. 
I tried to store a file date into the column. The file date was read out as 02h  40min, but Windows explorer displayed 14:40. Other files didn't have the 12 hour difference, so it's not generally the 12/24 hour-Problem.
[9 Aug 2012 9:04] Oliver Abraham
can be closed, it's not a bug.