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