Hi,
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.
(2) If DATETIME/TIMESTAMP values are internally saved UTC, what does mysqldump do with these values? Convert it to the timezone mysqldump is executed?
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).
(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.
Thanks to all who can provide more insight into these topics!
Michael Kofler, author of The Definitive Guide to MySQL (apress)
http://kofler.cc/mysql/mysqlbook.html
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
