Hi! --- "Michael Kofler" wrote: --- > >>>I read Trudy Pelzer's article on Temporal Functionality in MySQL > >>>4.1. > >>> > >>>http://dev.mysql.com/tech-resources/articles/4.1/time.html > >>> > >>>A few open questions to time zone internals remain: > >>> > >>> > >>>(1) How are DATETIME and TIMESTAMP columns saved internally? I > >>>would guess, UTC. One could also interpret the 4.1.3 changelog this way. > >>> > >>>http://dev.mysql.com/doc/mysql/en/News-4.1.3.html > >>> > >>>That is, values stored in such a column are normalized towards UTC > >>>and converted back to the current connection time zone when they are > >>>retrieved from such a column. > >>> > >>>But I found no way to make this really sure.
Datetime values stored in TIMESTAMP columns internally saved as number of seconds since the Unix Epoch (1970-01-01 00:00:00 UTC). So yes, one can say that they saved in UTC. Unlike for values stored in TIMESTAMP columns, date values stored in DATETIME columns are simply packed into 8-byte integers. For example datetime value '2004-12-14 19:00:02' is saved as 20041214190002. Values stored in such fields are not converted from session's time zone to UTC and backwards... In fact they are not affected by session's @@time_zone variable at all! Such conversion is done ONLY for TIMESTAMP columns. For example: mysql> set @@time_zone:='Europe/Moscow'; Query OK, 0 rows affected (0.07 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2004-12-14 19:20:57 | +---------------------+ 1 row in set (0.00 sec) mysql> create table t1 (dt datetime, ts timestamp); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (now(),now()); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +---------------------+---------------------+ | dt | ts | +---------------------+---------------------+ | 2004-12-14 19:21:43 | 2004-12-14 19:21:43 | +---------------------+---------------------+ 1 row in set (0.00 sec) mysql> set @@time_zone='America/New_York'; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +---------------------+---------------------+ | dt | ts | +---------------------+---------------------+ | 2004-12-14 19:21:43 | 2004-12-14 11:21:43 | +---------------------+---------------------+ 1 row in set (0.00 sec) So I won't talk about DATETIME columns further... > >>>(2) If DATETIME/TIMESTAMP values are internally saved UTC, what > >>>does mysqldump do with these values? Convert it to the timezone > >>>mysqldump is executed? It does nothing :) All time zone related conversions are done on server side (this is true for any MySQL client). mysqldump is relatively simple MySQL client which just connects to MySQL server and gets information about tables using standard queries... Since it does not specifies connection's (aka session's) time zone expicitly default session's time zone is used (Which is specified by setting global @@time_zone variable or using MySQL server's --default-time-zone=... option, or is 'SYSTEM' time zone if nothing was touched...). Server converts TIMESTAMP values from UTC to this time zone and sends them to mysqldump which simple stores data... > >>>To allow database migration between different timezones, it might > >>>be safer if mysqldump would deliver UTC times (or if there were at > >>>least another option for this). Good point. But I think that it is more sensible just add "SET @@time_zone:='default-time-zone-of-source-server'" statement at the beginning of mysqldump's output. This will ensure that same time zone is used for saving/restoring of TIMESTAMP values to/from dump. > >>>(3) With system, server and client timezones: How does the client > >>>know in which timezone it is running? How does it report this > >>>information to the MySQL Server? Has the C API a function to set > >>>the timezone? (I found none.) > >>> > >>>I made a small experiment: > >>> > >>>- My MySQL server (5.0.2) runs on Linux, system_time_zone=CET, > >>>global.time_zone=SYSTEM. > >>> > >>>- As a client, I used Windows 2000 set to time zone GMT-5 (New > >>>York) > >>> > >>>- Now I used mysql.exe (from MySQL 5.0.2, time zone set to New > >>>York) to connect to the MySQL Server (Linux, time zone CET). The time > >>>zone information form the client was not reported to the server. > >>>@@session.time_zone is SYSTEM (obviously wrong). > >>> > >>>This means: The time zone is not reported automatically, at least > >>>not in all cases. Yes, you are right. Client does not know in which time zone it runs and thus it indeed does not set session's time zone when connection is established automatically. BTW, here are some good reasons for not doing this: 1) Mapping between client's time zones and server's time zone may be non-trivial ... (e.g. imagine Windows client and MySQL's server which default time zone descriptions are Unix based). 2) Doing so will break compatibility with previous MySQL's versions. 3) Actually I think user's time zone and not client time zone is what important. Yes in case of some kind of GUI client they are the same but for most of web-applications they are not. So it is application responsibility to set MySQL session's time zone what it needs. There is no special C API function for setting session's time zone - one should use ordinary mysql_query/mysql_real_query() call and "SET @@time_zone:=..." statement (which is equivalent to "SET @@session.time_zone:=...") instead. Hope this makes all these a bit more clear. Thank you for your interest in these features and especially for your hint about mysqldump! -- Dmitri Lenev, Software Developer MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
