I'll go ahead and file several bugs, and start changing our code to avoid the timestamp type altogther. I'm quite surprised!
Peter
Peter Valdemar Mørch swp5jhu02-at-sneakemail.com |Lists| wrote:
*******************
Claim
*******************
I seem to have discovered that MySQL cannot handle the hour where DST becomes non-DST reliably (on Oct 31st in CET):
2am 3am | problem | | time | --->-----------DST---->----->----/ / |/ / / /---->------>--- non-DST ------>--------
Essentially, the problem seems to be that "October 31, 2:15 am" is ambiguous and MySQL cannot disambiguate between them.
It seems that internally MySQL stores timestamp values with all timezone and DST/non-DST information intact. But all operations (e.g. TIMEDIFF(), FROM_UNIXTIME() and UPDATE TABLE SET timestamp=timestamp+0) destroy this important information and operate only now()'s timezone ignoring the appropriate original source time zone.
I just find it impossibly difficult to believe I'm the first one finding this, so now I'm curious. Googling has come up short.
I very much hope I'm mistaken and that I don't have to change our code, database format and contents to store # secs since 1970 GMT in an int everywhere and avoid timestamps altogether because they "don't work"...
********************
Evidence for claim
********************
Essentially we have a log table using a timestamp column as a sort key. For our logic to work we need to be able to:
* Sort reliably, because the order of entries is very important (we use the "last" log entry to determine "current" state). Entries that are made during 2am-3am DST *must* come before log entries that are made 2am-3am non-DST just as they follow chronologically in real life.
This works initially, but gets broken by a "mysqldump db | mysql new_db" cycle. Timestamp values in mysqldump output don't contain DST timezone information and hence of course it is lost during restore. There is no option to mysqldump to maintain this info in output. How does one backup a database then? (Other than cp or mysqlhotcopy which fails if host != localhost)
* Be able to do TIMEDIFF() math and "INSERT INTO TABLE ... VALUES(FROM_UNIXTIME(?))" maintaining the correct time. This is not possible.
It seems that for all MySQL functions, the 2am-3am period (DST/non-DST) chosen depends on now(), not the value of ?, yeilding off-by-an-hour errors. As illustrated by this short example (more elaborate examples follow below):
susan:~# cat /etc/timezone Europe/Copenhagen
susan:~# mysql -e ' select 1099185600-1099181400; select TIMEDIFF(FROM_UNIXTIME(1099185600), FROM_UNIXTIME(1099181400));' +-----------------------+ | 1099185600-1099181400 | +-----------------------+ | 4200 | +-----------------------+ +----------------------------------------------------------------+ | TIMEDIFF(FROM_UNIXTIME(1099185600), FROM_UNIXTIME(1099181400)) | +----------------------------------------------------------------+ | 00:10:00 | +----------------------------------------------------------------+
But 4200 secs is 01:10:00, not 00:10:00!!!
Before we:
1) File an enhancement request against mysqldump
2) File bugs against UNIX_TIMESTAMP() and TIMEDIFF() et. al.
3) change our database format and code to avoid the timestamp type altogether and use "secondsSince1970 int" instead (huge)
, I'd love to hear that I'm mistaken and that these problems can be solved with current mysql code. I've tried 4.1.7 and 4.0.22.
*********************************
More examples
*********************************
I've created a table only with an ID and a timestamp. I've tried filling it in a loop in two ways (perl source code link below):
1) modifying system time and inserting into table using implied timestamp=now(). This works (order by timestamp - IDs are in order):
+----+---------------------+---------------------------+
| ID | timestamp | unix_timestamp(timestamp) |
+----+---------------------+---------------------------+
| 1 | 2004-10-31 01:45:00 | 1099179900 |
| 2 | 2004-10-31 02:15:00 | 1099181700 |
| 3 | 2004-10-31 02:45:00 | 1099183500 |
| 4 | 2004-10-31 02:15:00 | 1099185300 |
| 5 | 2004-10-31 02:45:00 | 1099187100 |
| 6 | 2004-10-31 03:15:00 | 1099188900 |
+----+---------------------+---------------------------+
But TIMEDIFF() on this otherwise correct data is erroneous: select TIMEDIFF(A.timestamp, B.timestamp) from table as A, table as B where A.ID=4 and B.ID=2; +------------------------------------+ | TIMEDIFF(A.timestamp, B.timestamp) | +------------------------------------+ | 00:00:00 | +------------------------------------+
This should have been a full hour!!!
An ever-so-slight modification (+0) on above table.timestamp makes it loose its timezone info:
update table set timestamp=timestamp+0
IDs are now out of order, when order by timestamp!!!!!
+----+---------------------+---------------------------+
| ID | timestamp | unix_timestamp(timestamp) |
+----+---------------------+---------------------------+
| 1 | 2004-10-31 01:45:00 | 1099179900 |
| 2 | 2004-10-31 02:15:00 | 1099185300 |
| 4 | 2004-10-31 02:15:00 | 1099185300 |
| 3 | 2004-10-31 02:45:00 | 1099187100 |
| 5 | 2004-10-31 02:45:00 | 1099187100 |
| 6 | 2004-10-31 03:15:00 | 1099188900 |
+----+---------------------+---------------------------+
^
| Out of numerical order!!!!
2) keeping now() in a single time zone and specifying timestamp=FROM_UNIXTIME($val) during insert. Specifying time fails (order by timestamp):
- timestamps are duplicated - IDs are out of order!!!!!
+----+---------------------+---------------------------+
| ID | timestamp | unix_timestamp(timestamp) |
+----+---------------------+---------------------------+
| 1 | 2004-10-31 01:45:00 | 1099179900 |
| 2 | 2004-10-31 02:15:00 | 1099185300 |
| 4 | 2004-10-31 02:15:00 | 1099185300 |
| 3 | 2004-10-31 02:45:00 | 1099187100 |
| 5 | 2004-10-31 02:45:00 | 1099187100 |
| 6 | 2004-10-31 03:15:00 | 1099188900 |
+----+---------------------+---------------------------+
"Extraordinary claims require extraordinary evidence." - Carl Sagan Sorry this is long but I couldn't make it much shorter...
************************** Source code to reproduce ************************** http://demo.capmon.dk/~pvm/mysqlTimestamps/timestamps.pl.txt Output of said code (WITH /etc/timezone == "Europe/Copenhagen"!!) http://demo.capmon.dk/~pvm/mysqlTimestamps/output.txt
I really hope someone has a good idea what to do now!
Peter
-- Peter Valdemar Mørch http://www.morch.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
