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.

Now that is a hella confusing. I do not have MySQL server to verify, lets see what others got to say. +1 :) — alandarev, Oct 31 '13 at 14:00
Time is like that. See [this answer](http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices) for a lot more info. — NovaDenizen, Oct 31 '13 at 15:10

zessx · Accepted Answer · 2013-10-31T14:47:27.843

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;

score 2 · Answer 2 · answered Oct 31 '13 at 14:10

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)

answered Oct 31 '13 at 14:10

Oleg Rogov

720
3
16

I assume your timezone is one where the switch between Daylight Saving Times has already passed. But thanks for testing it. It confirms the previous answer that this is a DST issue. – Christian Oct 31 '13 at 15:24

Problems with conversion from unix time

2 Answers2