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: | |
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
[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.