Bug #50627 | how to insert a TIMESTAMP of the last hour of daylight savings | ||
---|---|---|---|
Submitted: | 26 Jan 2010 15:21 | Modified: | 29 Jan 2010 22:23 |
Reporter: | Marc Herbert | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.5.1-m2 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[26 Jan 2010 15:21]
Marc Herbert
[26 Jan 2010 15:22]
Marc Herbert
Note that temporarily switching to the CET timezone (and restarting MySQL) is harmless for any Unix system. Alternatively, this problem can be reproduced in any timezone with daylight savings (to keep things simple, I assume that client and server are in the same timezone). 1. First find in which timezone MySQL is running: mysql test -e 'select @@system_time_zone;' => EST / EDT (for instance) 2. Pick up the last daylight savings hour of any year. For EDT let's use for instance 2 November 2008, 1am to 2am date --date='2008-11-02 1:30+EDT' +%s # last hour of daylight savings => 1225603800 date --date='2008-11-02 1:30+EST' +%s # first "winter" hour => 1225607400 3. Now try to insert 1225603800. And fail.
[27 Jan 2010 7:26]
Sveta Smirnova
Thank you for the report. Verified as described. Interesting what unix_timestamp('2005-10-30 02:25:10') unix_timestamp('2005-10-30 03:25:10') return different values : select unix_timestamp('2005-10-30 02:25:10'), unix_timestamp('2005-10-30 03:25:10'); unix_timestamp('2005-10-30 02:25:10') unix_timestamp('2005-10-30 03:25:10') 1130635510 1130639110
[27 Jan 2010 14:49]
Marc Herbert
> Interesting that unix_timestamp('2005-10-30 02:25:10') > unix_timestamp('2005-10-30 03:25:10') return different values I don't think that this is very relevant to this bug. What looks more relevant to me is that: select from_unixtime(1130635510), from_unixtime(1130631910); ... return the same values (when running in CET / CEST) One nice way to "fix" this bug would be to implement feature request Bug #50658
[29 Jan 2010 17:44]
Stefan Hinz
From Marc Hebert's comment, this is a server bug, not a docs bug.
[29 Jan 2010 22:23]
Marc Herbert
> select from_unixtime(1130635510), from_unixtime(1130631910); > return the same values (when running in CET / CEST) The above is not really a bug: the first value returned is in (omitted) CEST while the second, identical value is in (omitted) CET. It's more a design issue than a bug.