Problems with conversion from unix time

Question

I found something weird when trying to deal with unix timestamps in MySQL. I noticed that between '00:59:59' and '01:00:00' are more then 1 second when converting to the unix timestamps for an arbitrary date. Digging a bit deeper I have the following example query with a result I don't understand:

SELECT FROM_UNIXTIME(1382835600) AS a, FROM_UNIXTIME(1382832000) AS b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2013-10-27 01:00:00 | 2013-10-27 01:00:00 |
+---------------------+---------------------+

Both unix timestamps are 1h (3600s) apart, but the resulting timestamps are the same. Maybe it's a timezone issue, but it's on the same server after all.

Answer 1

Brace yourselves, winter is coming...

This is caused by the passage from summer hour to winter hour, depending on your timezone (see this link).

2013, Octobre 27th was the day where we came to winter hour (for European countries, at least). At 03 a.m, the hour became 02 a.m, which explain why you have the same hour for those 2 differents timestamps.

Extract from MySQL doc :

[FROM_UNIXTIME] Returns a representation of the unix_timestamp argument [...]. The value is expressed in the current time zone.

If I calculate well, you're working in GMT time.
Make another try a few hours later, you'll see everything is ok :

SELECT FROM_UNIXTIME(1382839200) AS a, FROM_UNIXTIME(1382842800) AS b;

Answer 2

Looks really strange, look what i got on version 14.14 Distrib 5.1.51:

mysql> SELECT FROM_UNIXTIME(1382835600) AS a, FROM_UNIXTIME(1382832000) AS b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2013-10-27 05:00:00 | 2013-10-27 04:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)