MySQL Bugs: #50627: how to insert a TIMESTAMP of the last hour of daylight savings

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.1-m2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It does seem possible to insert a TIMESTAMP value of the last hour of daylight savings.

How to repeat:
For instance in the CEST / CEST timezone:

CREATE TABLE a (ts TIMESTAMP);
INSERT INTO a VALUES (from_unixtime(1130631910));
SELECT unix_timestamp(ts) FROM a;
                                =>  1130635510  !

The explanation WHY this problem happens has been documented following Bug #15654. This new documentation is certainly interesting, but it provides neither a solution nor a workaround. In other words, we still do not know:

- How to insert a TIMESTAMP value of the last hour of daylight savings?
- Is this possible at all? (or is one hour per year lost in the void?)

I just want to insert the value "1130631910". I do not care which function I should use: just tell us.

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.

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

> 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

From Marc Hebert's comment, this is a server bug, not a docs bug.

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