Bug #6695 timestamps ambiguos in CET timezone
Submitted: 18 Nov 2004 9:59 Modified: 26 Nov 2004 10:11
Reporter: no forms Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:4.0.21-7 OS:Linux (debian sarge)
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: affects_connectors

[18 Nov 2004 9:59] no forms
We have a mysql-server running in Central European Time (CET).
The speciality of this timezone is that we skip an hour twice a year. In a night in spring, we skip the hour forward from 0200 to 0300, in autumn we skip back from 0300 to 0200, so this hour is repeated twice, every time in this period occurs twice.
This just happened on 2004-10-31.
Now the problem is that the internal representation of a timestamp in mysql and the time functions seem to ignore this fact.

How to repeat:
mysql> select from_unixtime(1099183200);
| 2004-10-31 02:40:00       |

mysql> select from_unixtime(1099186800);
| 2004-10-31 02:40:00       |

Notice that 2 different unix timestamp yield the same CET time. This is correct so far if the timezone difference would be added. In the first example it should be UTC+0200, in the second UTC+0100 hours. But I know of no way to get that information from mysql.

But it seems that the internal timestamp representation of mysql is neither a unix timestamp nor saves the time difference. Worst example:

mysql> select unix_timestamp(from_unixtime(1099183200));
|                                1099183200 |

Obviosly mysql does not take into account the difference from UTC. This is true not only for the time functions, but also the TIMESTAMP/DATETIME column types.

Suggested fix:
For most applications this one ambiguous hour per year should not present a problem.
But for more delicate applications (e.g. payment systems) the server should not be set to local time but GMT/UTC.
If local time is needed or wanted on the server, I recommend avoiding any of the mysql TIMESTAMP/DATETIME functions and do time calculations in another programming language. Also beware of the column types, and instead write the unix timestamp to a BIGINT column.
[19 Nov 2004 18:36] no forms
I ran the last example on the wrong machine. This is the real output with timezone CET:

mysql> select unix_timestamp(from_unixtime(1099183200));
| unix_timestamp(from_unixtime(1099183200)) |
|                                1099186800 |
1 row in set (0.00 sec)
[26 Nov 2004 10:11] Dmitry Lenev

Actually it is not the problem of internal representation. For example values in columns of TIMESTAMP type is internally represented as number of seconds since the beginning of Unix Epoch (1970-01-01 00:00:00 UTC), thus capable to distingiush such 'ambiguos'
moments of time. This can easily be seen if you have table with TIMESTAMP column which values are set automatically and records inserted around such 'ambigous' period of time. (Note that DATETIME type is internally just combination of original year/month/date hour:minutes:seconds values packed into 8-byte integer, and thus incapable to disinguish such values).

So it is more problem of representation of datetime values as strings. MySQL just does not support offset from UTC or time zone daylight saving abbreviations (like CET/CEST) as part of datetime string yet.

In case of ambiguity in 'datetime as string' to 'datetime representation capable to distingiush such values' conversions (which happen in unix_timestamp() function or when you store datetime value in TIMESTAMP column) MySQL will consistently prefer one of two possible values (in your case it is value which correspond to later moment in time).

We going to support offset from UTC and time zone daylight saving abbreviations as parts of datetime values in future. Unfortunately this require big changes in server and won't appear anywhere closer than 5.2.