Bug #15654 FROM_UNIXTIME / UNIX_TIMESTAMP probs with dbl. hour during summer/normal time
Submitted: 10 Dec 2005 16:51 Modified: 20 Jan 2006 18:41
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.18-BK, 4.0.24 OS:Linux (Gentoo - x86_64)
Assigned to: Paul DuBois CPU Architecture:Any

[10 Dec 2005 16:51] [ name withheld ]
Description:
The conversion from unixtime to internal format and back to unixtime eats up that single magic hour which is doubled during the switch from summertime back to the normal time.

I expect, that for every X the expression UNIX_TIMESTAMP(FROM_UNIXTIME(X)) == X is true. But for times within that magic hour, this is sadly not true.

How to repeat:
Wrong example:

mysql> select UNIX_TIMESTAMP(FROM_UNIXTIME(1130631910));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1130631910)) |
+-------------------------------------------+
|                                1130635510 |
+-------------------------------------------+
1 row in set (0.02 sec)

Correct example :
mysql> select UNIX_TIMESTAMP(FROM_UNIXTIME(1130031910));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1130031910)) |
+-------------------------------------------+
|                                1130031910 |
+-------------------------------------------+
1 row in set (0.01 sec)
[11 Dec 2005 15:32] Valeriy Kravchuk
Thank you for a problem report. Looks like the problem exists even in the latest 5.0.18-BK builds (ChangeSet@1.1992, 2005-12-09 00:58:59+03:00):

mysql> select UNIX_TIMESTAMP(FROM_UNIXTIME(1130631910));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1130631910)) |
+-------------------------------------------+
|                                1130635510 |
+-------------------------------------------+
1 row in set (0.04 sec)

mysql> select UNIX_TIMESTAMP(FROM_UNIXTIME(1130031910));
+-------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(1130031910)) |
+-------------------------------------------+
|                                1130031910 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18    |
+-----------+
1 row in set (0.03 sec)

At list, this "side effect" of summer time should be described in the manual.
[19 Dec 2005 13:54] Sergei Golubchik
It's a documentation issue.

UNIX_TIMESTAMP and FROM_UNIXTIME conversions are lossy, because datetime is specified in your local timezone and timestamp is in UTC.

So there are TWO different arguments (in CET) of UNIX_TIMESTAMP that return the SAME result value (in UTC).

mysql> select UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
[19 Dec 2005 14:04] [ name withheld ]
Sorry, I do not agree that this is a documentation issue.

I started with this problem:
####
mysql> create table a (f1 DATETIME);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into a VALUES (FROM_UNIXTIME(1130631910));
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+---------------------+
| f1 |
+---------------------+
| 2005-10-30 02:25:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> select UNIX_TIMESTAMP(f1) from a;
+--------------------+
| UNIX_TIMESTAMP(f1) |
+--------------------+
| 1130635510 |
+--------------------+
1 row in set (0.00 sec) 
####

So here you can see, that I fill one value into the database and get a different one back. It shall never matter what value I fill in, the same shall always come back. UNIX_TIMESTAMP is seconds since 1970, and there is no such thing like a timezone in this unix-time format. And there is no missing second in that format, every single second does exist.
[20 Jan 2006 18:41] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

It is a documentation issue. The mapping isn't 1-1 in both directions,
so it must be lossy.  I'll add this to the description for UNIX_TIMESTAMP():

Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert
between TIMESTAMP values and Unix timestamp values, the conversion
is lossy because the mapping is not one-to-one in both directions.
Because of daylight saving time, it is possible for two UNIX_TIMESTAMP()
to map two TIMESTAMP values to the same Unix timestamp value.
FROM_UNIXTIME() will map that value back to only one of the original
TIMESTAMP values.  Here is an example, using TIMESTAMP values in 
the CET time zone:

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+

mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00       |
+---------------------------+
[19 Jan 2010 22:31] Marc Herbert
So you have successfully explained and documented why:

  insert into a VALUES (FROM_UNIXTIME(1130631910));

does NOT insert the value 1130631910

Now please explain and document how to actually insert this value into a timestamp column (for @@system_time_zone = CET). Or clearly document the fact that it is not possible.

Note: this problem is obviously not just for 1130631910 but repeats every year.