php - Storing time information: Timezone required?

Question

I'm curious to know if what I'm considering is bad practice, or if since this is a specific and deliberate choice it is actually a decent idea. I want to store information for dates for events that occur in specific cities. I want to store that data as UTC timestamps. Wouldn't it be a good idea to simply store the timestamp and the city id/country id (which is associated with a specific timezone), rather than storing the timezone for each event? I ask because timezones can change, but city IDs would never change in the DB. Once the server is synced with the latest timezone in the (unlikely) event of a timezone change, the event would be independent and unaffected by that change. However, say a timezone changes its boundaries, then events that occurred in that timezone previously could be outside of it. Does it seem unwise to do this? I'm just wondering, and I've been scouring for best practices but in this case this actually seems like an OK idea. This works particularly because the application design model would never change- events will ALWAYS be associated with a specific city.

The basic flow would be:

• Event data with date/location comes into the system in a standard format like ISO-8601 YYYY-MM-DD string.

• System converts date to UTC timestamp and stores the date with the event using that timestamp and the city ID for the event.

• When a user requests to view that event, the system pulls the timestamp and city information associated with that event, and uses the city's timezone to format the date accordingly on display.

Is this a terrible idea? Is there a benefit to this, and is the concept of storing the TZ Offset the same idea to eliminate this issue?

Answer 1

Whichever way you do it, it will fail in different ways depending on what is changing.

1. If you store timestamps in the according timezone as 2013-12-29 12:34:56 America/New_York, this will fail if, say, the Bronx suddenly starts their own timezone America/New_York_Bronx with a different offset and your event happened to be in the Bronx.

   Decide how likely this is and how bad a failure would be.

2. If you store timestamps in UTC and the timezone in which the event is happening is redefining their offset (e.g. shifting DST dates around, or entirely shifting to a different offset), the event time may differ from the actual wall clock time at that location. If you store 2013-12-29 12:34:56 UTC for an event at 13:34:56 in Berlin, Germany, and Berlin shifts their DST around, 2013-12-29 12:34:56 UTC may now correspond to 14:34:56 Berlin local time, while the event is still actually happening at 13:34 local time.

   Decide how likely this is and how bad a failure would be.

3. If you store the UTC timestamp and link it to a physical location which you then link to a timezone, you can counteract both problems. But for this you'll have to store the precise physical location, not just "New York", otherwise you just have case 1. with one more intermediate step. If you do store the precise physical location and have a precise way to resolve this location to a timezone and you keep your timezone database up to date, you can handle pretty much all change scenarios.

   Decide how practical this is and how much this extra precision is worth to you.