MySQL Bugs: #6695: timestamps ambiguos in CET timezone

Bug #6695	timestamps ambiguos in CET timezone
Submitted:	18 Nov 2004 10:59	Modified:	26 Nov 2004 11:11
Reporter:	no forms
Status:	Won't fix
Category:	Server: Types	Severity:	S2 (Serious)
Version:	4.0.21-7	OS:	Linux (debian sarge)
Assigned to:	Dmitri Lenev	Target Version:
Tags:	affects_connectors

Description:
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.

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)

Hi!

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.