Hi everyone, Apologies, I'm coming very late into this conversation, but perhaps I can disperse some of the confusion...
Rob's original question: ***** I'm curious about how MySQL deals with time zones. Our machines are located in the GMT time zone, and recently rolled over to British Standard Time (local daylight savings time). The MySQL installation has started returning NOW() values in local time, not GMT. Is this the way the database is designed to work? With no time zone sub-field in date and/or time fields, how are you supposed to deal with a clock which, because of daylight savings time, will move an hour back in time once a year? What does the SQL standard say about time zones and functions like NOW(), upon what standards has the database community converged for dealing with time zones, and how can MySQL be configured to provide consistent (not affected by daylight savings time) values for NOW()? ***** Keith's most recent contributions: > > Using UNIX_TIMESTAMP() on a > > timestamp column will produce an epoch time (in seconds), but it > > won't necessarily be the right epoch time, because of daylight saving > > time (summer time) and possibly data that has moved from one time > > zone to another. > Actually the documentation seems to say that that's not true. > I haven't tried it, so it could very well work. Manual References: 6.2.2 Date and Time Types 6.3.4 Date and Time Functions A.4.6 Time Zone Problems F Environment Variables MySQL does not keep track of time. Accordingly every single time call is referred back to the underlying operating system. Having said that, I have not messed with the TZ setting (Appendix F) to see what happens if this is not set to the same as the OpSys ToD clock. Further MySQL is quite forgiving (some would say "loose") about temporal values - it allows zero components in dates and in some cases illegal dates, eg 31st day of short months. The manual (6.2.2) puts responsibility for such values on the shoulder of the interfacing application. Warning: The MySQL UNIX_TIMESTAMP functions return local time! (see annotation/comment at 6.3.4) or conduct an experiment for yourself - I always find the wording of these things mildly confusing because the UNIX Epoch is defined to have begun at a time expressed in GMT. However all Timestamp values (SQL or UNIX) are 'modified' to refer to local time, ie 000000000000000 was the time in GMT but it was already 00000000003600 in a large portion of Europe (for example). These two behaviors offer you (Rob, et al) two choices: 1 If you are administering a db which requires continuous time, then it must be run as GMT and not allowed to (helpfully) update to BST (Rob's original question talks in terms of British time zones, but the same applies to any TZ subject to Summer Time/Daylight Saving). This is fine if your db server is not also something else, eg a mail server or web server. Obviously you would have to clearly identify that any times used by every application/service on the box as being GMT not 'local time'! 2 The other approach is to make sure that the interfacing application (eg I use PHP) converts dates/times appropriately. If we are talking about collecting data from a user form, then 'English' date formats already have to be converted to the ISO CCYY-MM-DD format, so also converting times to GMT (per this example) is not a 'big ask'. Obviously any data taken from the database will also need to be converted/localised. The PHP folks kindly provide a set of 'GMtime' functions to ease my life. (thanks guys!) AFAIK the MySQL implementation is the same as any SQL standard. (sorry can't offer a page reference - the SQL standards are still some way down my reading pile!?) Regards, =dn PS Rob if you need specific help, contact me directly and I'll send my Orange phone nr (London) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
