4

I'm building an application with events in different countries and cities. An event is always presented in a local time of a place where it occurs. Therefore I'm thinking that I won't need a time-zone information along with a date-time of an event in database.

Are there any arguments against this? Should I change my mind and store a time-zone or GMT+N of an event embedded into its "start_date_time"?

Improve this question
3
  • This completely depends on the requirements. If there is no crossboarder time calculation in your whole application, and you are sure this kind of requirement will never arise, then you can stick to local times. But if you are unsure, better store the information. – Doc Brown Sep 11 '17 at 5:38
  • @DocBrown, everything always depends on one's requirements – Dari Sep 21 '17 at 7:28
  • 1
    Yes, and since we don't know if you are going to have crossboarder time calculation in your application, but you should know if you have or expect this requirement, you can probably answer the question better than everyone else here. – Doc Brown Sep 21 '17 at 8:59
9

tl;dr

  1. Store dates and times in UTC for comparability, sortability, easy conversion.
  2. Also store IANA time zone as an attribute of the event if you need to define what tz to use for display regardless of user's current location.

More Details

In my experience the most widely recommended and used pattern is to store dates and times in Etc/UTC timezone (IANA Timezones) and to display dates and times in local time of the user viewing the information. This makes dates and times easily comparable and sortable on the server side and easily convertible to local time for display.

If your requirement is only to display local times and dates in a browser or native application based on the user's location, there is no need to store time zone for each time. Good UI tools, including browsers can convert UTC times and epoch times (which are by definition UTC) to local times.

If you have other requirements you may need to store timezone. You need to also store time zone if...for example...

  1. You want to display an event's dates and times in local time where the event takes place, regardless of where the user viewing the information is currently located. (I believe this was your requirement.) Time zone should probably be an attribute of the event.

  2. You ever need to produce output in a user's or event's local time from your servers. Examples: pdf's, e-mail content, paper that is produced on schedule and not directly in response to a user's request. In this case time zone could be a user preference, or an event attribute.

If you are going to store timezone you should generally use IANA time zones such as Etc/UTC and America/Los_Angeles. They are the most widely supported by development tools, time libraries etc. They are convertible to windows time zones (see this question)

Not Enough Details? - Why are offsets different from time zones?

Storing a UTC Offset such as -08:00 is not the same as storing a time zone. A UTC offset is valid to store, retrieve and display a specific time such as 2017-09-09T12:00:00-08:00. It allows you to display that local time at any time in the future and to convert it to UTC.

Depending on the tools you use, storing an offset may not meet your requirements. For example:

  • The offset alone does not tell you what time zone the event is in.
  • If you need to record or schedule another event on a different date at the same location, you can't just assume the offset from the last event is re-usable.
  • The offset -08:00 can be America/Los_Angeles or America/Anchorage or America/Tijuana, for example. Mexico and the USA start DST on different dates since 2007. So you need a time zone to know what offset to use on a new date.
  • You can't figure out an event's time zone reliably from the UTC offset of the user's browser or local OS.
  • Even if you get user's timezone from one of the browsers that supports it or from their local OS, is that user on a beach in Mexico while planning an event in Los Angeles?
  • So when you record an event, how do you know which offset to use?
  • Will you ever need to present time zone to users? Will you say: "Come to our big bot battle in San Diego or watch it live on botwars.net at 8pm UTC -07:00!"

Your requirements may dictate that you have more need to know the time zone of an event than the offset of any times associated with the event. This allows you far more flexibility in what you can do later. You can always recreate offsets from a time zone using a good time library. You cannot always reliably infer timezone from an offset.

Should you implement all of this yourself? No. There are plenty of good tools to help. Be sure you read the limitations of database time types and other tools before you use them. There have been a surprising number of problems with language and dbms support for time, and fortunately quite a few time/date libraries that pick up the slack.

Improve this answer
6
  • Everything in tl;dr is covered by the TIMESTAMP WITH TIME ZONE type that's been around since SQL 92. There's nothing wrong with storing offsets since only one can be in effect at any time in any location. Right now it's 2017-09-10T09:10:50-04:00 where I live and that will not change after we switch back to standard time. – Blrfl Sep 10 '17 at 13:10
  • @Blrfl: OP didn't say SQL, or which SQL. TIMESTAMP WITH TIMEZONE is one way to store offset or tz (depending on dbms implementation) with every date if your db supports it and that meets your requirements. Even RDBMS's are far from agreement on level of support or implementation for this. No one over about 47 years old can have a birthday in MySQL's TIMESTAMP type. No 30 year mortgage or bond maturity dates can go there. By all means use these types if you understand their limitations and they meet your requirements. – joshp Sep 10 '17 at 19:25
  • Point taken SQL not being in the question; that was my oversight. The agreement for RDBMSes is the ISO SQL standard, and PostgreSQL, Oracle, DB2 and SAP ASE (nee Sybase) don't seem to have a problem meeting it for that type. MySQL's a pathologically-bad case for this, but its developers have also made no bones prioritizing what the users want over standards compliance. – Blrfl Sep 10 '17 at 21:20
  • @Blrfl Sure. ISO SQL's WITH TIMEZONE types store UTC time and an offset to local time. SQL provides operations on those types to allow comparing and converting to UTC or local time. It's good, if you have it. The SQL standard did us a disservice by calling a UTC Offset a time zone. Java 8 got it closer to right with types Interval, OffsetDateTime, ZonedDateTime, LocalDateTime. – joshp Sep 10 '17 at 23:18
  • hence, do you suggest using event_date_time: timestampt with timezone + a separate column for event_offset: varchar? – Dari Sep 22 '17 at 1:46
3

If you store local times, you could have a problem with daytime saving time or any other timezone change.

To achieve this an hour is "taken away" or "added". The first case could make you think two events that happened one hour away from each other happened 2 hours away. In the second case, events that happened on two different hours will be mixed on which happened first (or an event that can happen only once an hour will not be allowed to happen in a different hour, since both seem to be the same).

A simple solution would be to store all the date-times on UTC 0 and adjust them to fit the place where they belong. In this way, you'll always have a certain point in time, so no data lost, and you'll have to adjust it to the corresponding place.

Improve this answer
7
  • If you store local times, you could have a problem with daytime saving time or any other timezone change. -- I don't understand – Dari Sep 10 '17 at 1:24
  • time-zone is already implicitly embedded into an event because an event occurs at a certain city which belongs to a certain time-zone – Dari Sep 10 '17 at 1:26
  • Let's say that event X happens at my city at 11:30pm (UTC -3). Because there is a timezone change, at 12pm, we change the time to 11pm again. Another event, Event Y, now happens at 11:15 (UTC -4). You saved event X with time 11:30pm and event Y with time 11:15pm. It seems like Y happened 15 minutes before, but it didn't, it happened 45 later. But you have no way to notice it. – Maximiliano Sep 10 '17 at 1:33
  • 4
    Some countries, like the US, or I believe most of the EU, change their time on summer. That means that on some day, on the USA for example, after 1:59am the clocks are changed to 3:00am. It skips one hour. On other day, after the end of the summer, after 1:59am, clocks are changed to 1:00am. This means that, on the same day, you have 25 hours and the 1:00am-1:59am happens twice. If you only save an event with "1:30am" there's no way to know if that happened at the same time that another event (on the same place) at "1:30am". You might want to read about daylight saving time. – Maximiliano Sep 10 '17 at 3:27
  • 1
    @Dari I would like to share this wonderful and informative video on the pain of dealing with time. Hopefully you will gain some insight into the woes of dealing with time and why these suggestions are being made. – Robzor Sep 10 '17 at 14:32
3

If you only store local time then you can't use it for calculations.

For example. Lets say I'm catching a flight from London to Paris. London is 1 hour behind Paris, my flight leaves at 10am and arrives at 1pm. How long is the flight?

Or, say I want to wake up early to watch the big fight, which is taking place in Las Vegas at 11pm (PT) But I will watch it on live TV in New York. Will it clash with my favourite show at 9pm (EST)

Just storing the UTC offset ie ISO 8601, is also flawed. Daylight saving times change the UTC offset of a time, so 2300+0100 plus 2 hours might not be 0100+0100 (or rather it will be, but the 0100 bit isn't the local time anymore)

It's usually best practice to store all dateime times in UTC and convert to the required local time when you display them. Which requires the time zone.

Improve this answer
0

Properly dealing with dates, times, calendars, timezones, etc is something that sounds simple but is notoriously difficult to get right. So first thing first, is to ask yourself, is this:

Does this need to be 100% correct, or does it have to just be mostly right but I can handle some occasional errors from time to time?

Well, regardless what the answer is, I don't think the OP has a choice and has to go with the latter, because one of the OPS comments:

If you store local times, you could have a problem with daytime saving time or any other timezone change. -- I don't understand

Tells me the OP is seriously ill-prepared for doing this correctly.

So, to answer your question, you absolutely should keep track of timezones. I didn't even read the actual specifics of the question, because I know that when it comes to dates and times, if you want to do it right, you have to go all the way. But before starting any programming, do some research into the difficulties of proper date-time handling.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.