MySQL Bugs: #27469: from_unixtime isn't consistantly reporting time correctly

Bug #27469	from_unixtime isn't consistantly reporting time correctly
Submitted:	27 Mar 2007 10:49	Modified:	27 Mar 2007 15:07
Reporter:	James Shirley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	4.0.24	OS:	Linux (debian linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

When i run the following command i don't get what is expected, 

mysql> select FROM_UNIXTIME(1174784399), FROM_UNIXTIME(1174784400);
+---------------------------+---------------------------+
| FROM_UNIXTIME(1174784399) | FROM_UNIXTIME(1174784400) |
+---------------------------+---------------------------+
| 2007-03-25 00:59:59       | 2007-03-25 02:00:00       |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

I've added one second between those requests and ended up with an hour and 1 second diffence.

I assume this has something to do with daylight savings (UK, london) time which on the morning of this day went forward an hour..

Shouldn't daylight savings kick in at midnight not at 1AM? I.e. we shouldn't ever be able to get the seconds between midnight & 1AM, on this day?

This is not that important, but good to know about, when seeing issues taking offsets from sunday morning (on daylight saving changes).. Maybe its easier to never take offsets from sunday mornings, which seems to be when daylight saving changes occur..

How to repeat:
mysql> select FROM_UNIXTIME(1174784399), FROM_UNIXTIME(1174784400);
+---------------------------+---------------------------+
| FROM_UNIXTIME(1174784399) | FROM_UNIXTIME(1174784400) |
+---------------------------+---------------------------+
| 2007-03-25 00:59:59       | 2007-03-25 02:00:00       |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

DST in Europe kicks in at 1AM UTC, so 1AM in the UK, 2AM in central europe and 3AM in east europe, and not at midnight.

http://en.wikipedia.org/wiki/Daylight_saving_time_around_the_world#Europe

ok that makes sense!

why didn't I think of that!

cheers