3

This is a conceptual question, so no code snippets here.

Say I create a database of events. Some of them are in New York, some in Chicago, some in Phoenix, etc...

My server's timezone is set to New York.

In my mind, I have two options when creating UNIX timestamps for all these events.

  1. Take the timezone into account. (i.e., An event at midnight on January 1 in Chicago and Pheonix would have different timestamps). Then I'd have to take the timezone into account again whenever I want to display the date in text format.

  2. Fudge it by pretending that all events happen in New York. An event at midnight on January 1 in Chicago and Pheonix would have the same timestamp. Since my server is set to New York, I wouldn't have to take the timezone into account for each event.

Which approach is better? Approach 1 gives a more "true" timestamp, but approach 2 seems less complex while still giving the same result.

Sam Nabi
  • 87
  • 6

2 Answers2

3

Everything should be stored in UTC so that there's a consistent basis for comparison and calculation. It should then only be converted to local time when some human gets involved.

The use of UTC will greatly simplify your code since you won't have to carry timezones around with every date/time and perform complex calculations across many different zones - we did this on a project once and I'll quit before I ever do it again :-)

In other words, if a user enters New York time, convert that to UTC as soon as possible. When presenting a date/time to a user, change it back to their local time as late as possible.

And, unless you have a real, pressing need (for example) to tell someone in Mumbai what the local time was in New York when the NY event happened (or will happen), you don't need to store the timezone for the event. If you do have such a need (a), by all means store the timezone as well, so you can get both local time where you are and local time where the event is/was.

(a) CJBS points out a few good examples in comments below, such as:

  • a flight where it's useful to know the local time of arrival at the destination such that a taxi can be ordered there; or
  • arranging with people at the event to meet at a certain time.
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Along with the timezone / UTC offset – CJBS Feb 27 '14 at 07:40
  • @CJBS, if you need the timezone of the event, you can do that. I've never found it necessary since people usually want to know when something happened based on _their_ timezone. But it's worth mentioning, so I will. – paxdiablo Feb 27 '14 at 07:45
  • 1
    Fair point. The question was conceptual, and related to events, so there /may/ be a need to record the timezone of the event in order that it be displayed in local timezone format (of the event, not of the end-user). Just a consideration. Consider a flight, for example, it's useful to know the local time of arrival at the destination such that a taxi can be ordered there. The same might apply for an event (arranging with people at the event to meet at a certain time in the event's timezone, regardless of where a person is right now). – CJBS Feb 27 '14 at 07:48
2

If you mean that you are recording the time of events as they happen, or recording the times of events in the past, then paxdiablo's answer is correct. Usually, UTC will suffice for that. In a few cases, you might want to store a local datetime + offset (a "DateTimeOffset" in some platforms), but that depends on exactly what you're using the data for.

However, if you are asking about scheduling an event that will happen in the future, especially if it's a recurring event, then UTC is not entirely sufficient. I've already written about this several times, so I suggest you read these articles for details:

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Your answer is more relevant to my situation than @paxdiablo's, since I'm dealing with events in the future. I hadn't thought about how to deal with timezone borders shifting in the future. – Sam Nabi Mar 03 '14 at 12:07