Time zones / GMT Offset

middleman666

Hey All,

I have a website that puts on events all over the world and I need to be able to display a list of upcoming events based on the time zone that event is taking place in.

What I've been doing is adding the GMT Offset to my countries table, so I can then link that to the events based on the country of the venue. I then work out the GMT time (server time) based on the local time set on the event like so:

SELECT event.Date AS EventLocalDate, 
DATE_ADD(event.Date, INTERVAL(-c.GMT_Offset) HOUR) AS GMTRemoveFromListingsAfter 
FROM events AS event
INNER JOIN venue AS v ON event.VenueID = v.id
INNER JOIN countries AS c ON v.Country = c.Country
WHERE DATE_ADD(event.Date, INTERVAL(-c.GMT_Offset) HOUR) > NOW()
ORDER BY event.Date ASC

Trouble is, I've been told that many countries have daylight saving times (not just those that work on GMT) so I'm a bit confused as to how to take this into account when reading out a list of upcoming events.

Would I need to update the GMT offset of the countries affected every time they change?

And as daylight saving doesn't always fall on the same date each year, is there a way I can keep my GMT offset up to date without having to check the daylight savings date each year for affected countries?

Cheers

johanafm

Convert all datetimes to the same datetimezone before storing them, and then convert them to each user's datetimezone after retrieval. This way your timezone handling will automatically include DST corrections.

middleman666

Thanks for your response.

I think that may be the question I need to ask; How do I handle daylight saving times?

Even if I stored all the dates in GMT, I'd still have to do a similar query to display the event date correctly... which would probably be a bigger job as the event date is displayed in a lot of places.

Storing the GMT offset on the countries table probably isn't the best idea as countries can have multiple time zones. I've tried compiling a list of time zones and ended up with hundreds. Do you think I should be storing them all or just a select few to cover the different offsets and zones?

Cheers

johanafm

It just struck me that if you are only displaying event data for the purpose of visiting these events IRL, then you might as well store all times in local time. After all, if I live in Central Europe and are checking out events in New York for my vacation there, do I really need to know when they happen if I stayed at home?
If you on the hand rather (or also) are displaying event data for the purpose of watching events online / TV / radio, then you would indeed need to (also) convert times to the user's timezone.

Assuming an event is entered into the database by a person, and furthermore assuming that said person is aware of the event-local timezone, they could specify the timezone when creating the event. If this is an ok approach, you could easily list the possible timezones in a select box by using the event's target country's two letter ISO 3166-1 country code.
For example, assuming the event is taking place in england, using country code GB will list Europe/London as possible timezones.

$li = DateTimeZone::listIdentifiers(DateTimeZone::PER_COUNTRY, 'GB');

$out = "<select>\n";
foreach ($li as $tz)
{
	$out .= "<option>$tz</option>\n";
}
$out .= '</select>';
printf('<pre>&#37;s</pre>', htmlentities($out));

If you need to automate the timezone conversion process, you would need to create separate tables for timezones, countries and cities, where city also includes a timezone foreign key. An event would then store its location as ids relating to the city table, which in turn give you the timezone. And when storing an event, the location has to be known, which would enable a lookup for the timezone.

middleman666

Cheers for the reply.

Just to confirm, the events I'm talking about are things like concerts/gigs which at present have the local time set as the event date.

So lets say I was putting on an event in New York, I select the country of the venue which would then populate the available time zones using the script you supplied and I select 'America/New_York' which I then store in the venues table...

The server time is set to Europe/London, with the current DLS time, the time in New York is -4 hours. Lets say the date and time of the event in New York is 2011-04-18 19:00:00 and that's what's stored on the database as event date; How would I display that event as an upcoming event using an sql query?

If I do SELECT * FROM events WHERE Date > NOW() then that event will not be listed after 19:00 GMT. If I was in New York or anywhere else browsing the events, I'd want to be able to see that event listed until the local time of the event e.g. 19:00 EDT.

What's the best way of making that happen? Should I store a GMT_Offset in a Timezone table like:

Timezone | GMT_Offset
America/New_York | -4

Or is there a way to do it with built in functions?

Would I have to manually adjust the Timezone table to account for daylight savings?

Cheers

johanafm

Just convert the event's datetime from UTC (or whatever timezone you chose to store datetimes in) to the event's timezone when you display them, and always use UTC (or whatever timezone you chose...) when querying the database.

header('content-type: text/html; charset=utf-8');

# example event data - retrieved by SQL query using UTC to check that events havn't allready begun
# also, times in retrieved data is obviously in UTC as well
$events = array(
	array('id' => 1, 'start' => '2011-04-18 19:00', 'end' => '2011-04-19 17:00', 'name' => 'Klubnacht', 'city' => 'Berlin', 'timezone' => 'Europe/Berlin'),
	array('id' => 2, 'start' => '2011-04-18 17:00', 'end' => '2011-04-18 22:45', 'name' => 'Bj&#246;rk', 'city' => 'Akureyri', 'timezone' => 'Atlantic/Reykjavik'));

$html = '';
$utc = new DateTimeZone('UTC');
foreach ($events as $e)
{
	$tz = new DateTimeZone($e['timezone']);
	$start = new DateTime($e['start'], $utc);
	$start->setTimeZone($tz);
	$end = new DateTime($e['end'], $utc);
	$end->setTimeZone($tz);
	$html .= '
<h3>'.$e['name'].' ('.$e['city'].')</h3>
<div><em>Start </em>'.$start->format('Y-m-d H:i').'</div>
<div><em>End </em>'.$end->format('Y-m-d H:i').'</div
';
}

echo $html;

And as you can see, the Berlin event is advanced two hours (since Berlin presently has summer time which means it's UTC +2, while the Akureyri event is unchanged since Iceland always has UTC (no use of summer time).