As I understand it, the mysql TIMESTAMP type represents the time in your local timezone (the one your computer is using).
This makes it problematic to use in timezones that have a summer/daylight savings time. All of the date arithmetic functions will yield inaccurate results if one of the dates is in summer time and the other is in standard time. For these reasons, I resort to using an INT field that stores "Unix" time (seconds since the start of the Unix epoch, 00:00:00 UTC, January 1, 1970). This makes the data independent of the database's time zone. I still use the TIMESTAMP data type as a convenient way to keep tabs on when a row was modified, if doing date arithmetic on the column is a concern. Erik Rantapaa [EMAIL PROTECTED] On Fri, Apr 12, 2002 at 09:26:33AM -0400, Bradley Brown wrote: > I would like to second this notion if I may. > I was up until midnight (EST) last night trying to come up with the very same >answers. > I found no answers from either the online mailing list searches nor from the O'Reilly > Mysql book... nor from the DBI book. > This also makes it difficult when porting schema from one dbase to another as I'm >doing > now with a portgresql dbase that supports a "timestamp with time zone" type. > > I would also be interested in any info anyone can provide on this subject. Thank you > very much. > > Bradley > > Rob wrote: > > > 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()? > > > > --------------------------------------------------------------------- > > 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 > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
