Re: MySQL GMT --> Local time

On Thu, 17 Oct 2002, 1LT John W. Holmes wrote: > > My logging application is feeding a MySQL database with data records > > that are time stamped with GMT time. I would like to query the database > > for records matching local time(eg. all records created on oct > > 17,2002 local time). I would prefer if the records could be formated > > in local time when returned from MySQL. What is the best way to do this. > > I don't know if there is an automatic way to do it, but if you know you are > 5 hours ahead of GMT for example, and you want all records for Oct 17, 2002 > GMT+5, then you could do this: > > SELECT * FROM your_table WHERE TO_DAYS(datetime_column + INTERVAL 5 HOUR) = > TO_DAYS(20021017) > > You can use DATE_FORMAT() in your query to reformat the MySQL timestamp, or > use UNIX_TIMESTAMP() to retrieve a unix timestamp instead and format it in > PHP. With the above query, the "5" and actual date "20021017" can be PHP > variables, to make it more flexible. > > ---John Holmes... > Where it gets messy is when daylight saving kicks in. Knowing what dates are included with standard or daylight savings time offsets is a problem. It gets more complicated when I want to return a distinct date record set from the timestamp column. Jason