MySql GMT Dates and Timezones

Question

I have php application where I store event dates in GMT +0000 within a timestamp field (mySQL). Please ignore the bulk of the code below as it comes from a function where I am dealing with other timezones - its just to show that I am converting any date input to GMT +0000 for storage in the db. For the purposes of this question the offset = 0.

date_default_timezone_set('GMT');
$gmtTimezone = new DateTimeZone('GMT');
$userTimezone = new DateTimeZone('GMT');
$myDateTime = new DateTime(date('Y-m-d H:i'), $gmtTimezone);
$offset = $userTimezone->getOffset($myDateTime);
$event_date = date('Y-m-d H:i', $myDateTime->format('U') + $offset);
$q = "insert into time_zone(dtime) values('".event_date."')";
$r = mysql_query($q);

And this seems to work well. So even if my server is running in the USA the date stored in the field is London date/time.

Now my challange is this. My clients need to locate events that occured within a date/time zone but they will work on their own timezone. For example Pacific/Auckland (GMT + 13).

So if the client enters in a search date of start "2010-12-30 00:01" and an end date for the search of "2010-12-30 23:01" how do I pass in a SQL statement that basically tells the database to search for values in the date field (stored as GMT + 0000) but add 13 hours (or negative) since the timezone is Pacific/Auckland. Is there an mySQL way of doing this taking into account Daylight savings etc...

So if a GMT date of "2010-12-30 08:17:00" is stored and my client living in Auckland searches with the dates above this record "2010-12-30 08:17:00" should come up because GMT +13 covers the search.

Answer 1

The client should convert the datetimes from local time to GMT / UTC before sending them to database.