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: | |
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 ]
[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.