4

I'm working on a world-wide scheduling service which uses physical locations in different time zones. These time zones must be persisted in the database along with each location. The question is, how are they best stored?

We currently use a custom time zone table, which maps custom integer IDs to Microsoft time zone string identifiers. I wish to store the IANA time zone identifiers instead. Our database is an SQL Server, which is accessed in C# using Entity Framework 6. We handle time using NodaTime. A solution must work well with all these technologies.

I see two different ways to do it:

  1. Simply store the IANA identifier as a string along with each location.
  2. Store all IANA identifiers in a separate table and use a foreign key to link to it.

The first solution is probably the easiest, as it easily allows for new identifiers and it keeps the data closely together. It does, however, have the downside of using a lot of space.

The second solution requires us to join on the time zone table every time we need the timezone - which is rather often - but requires little space. New time zone identifiers must be added to that table if needed. It also introduces these magical integer IDs (the foreign key used), which might be mistaken as being commonly known identifiers (we currently have this problem, where IDs have moved out of the database and into an in-code dictionary used instead of the database table).

As I'm writing this, I'm wondering, if it could even be possible to create a custom time zone UDT for SQL Server, where time zones can be saved and loaded as their string identifiers, but be stored more efficiently in a user-hidden format.

Mikkel R. Lund
  • 2,336
  • 1
  • 31
  • 44
  • Why not just store everything as UTC and handle localisation in the client application? – iamdave Dec 06 '16 at 13:03
  • The locations could for instance be a restaurant, which has local opening hours. These opening hours are based on the restaurant's location, not the viewer's. Furthermore, we do scheduling, so future times must be stored in local time, so they can be converted correctly into zoned times based on the given location's time zone. Furthermore, one of the clients is a JS frontend, and JS is a mess when it comes to handling time zones and daylight saving time. – Mikkel R. Lund Dec 06 '16 at 13:11
  • 1
    Storing dates in local time is generally a bad idea as local time changes with daylight savings etc. If you store everything in UTC and maintain your timezone lookup table you can adapt to local changes as well as accurately and simply convert to any time zone, be it the user or the location in both the future and the past. Trying to store it as local with give you much larger headaches than simply trying to display it as local. – iamdave Dec 06 '16 at 13:19
  • 4
    Storing local times is necessary for scheduling future events, in case say daylight saving time rules change. The local time could map differently if the rules change. Please, I know how to handle time. I'm asking how I should store time zones of physical locations in a database in the best possible way. – Mikkel R. Lund Dec 06 '16 at 14:32
  • 4
    @iamdave - Mikkel is correct with regard to scheduling. "UTC Always" is not necessarily true - there are lots of edge cases. This is covered in http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices – Matt Johnson-Pint Dec 06 '16 at 17:30

1 Answers1

8

While either approach will work, the common practice is just to store the IANA time zone identifier as a string. They are indeed unique identifiers, so they can be treated as such. "America/Argentina/ComodRivadavia" is currently the largest string, at 32 characters - so a varchar(32) would suffice. Though, I typically use a varchar(50) just to be future-safe.

The few kilobytes of storage you may save by normalizing to a lookup table usually are not worth the perf-impact of the join, IMHO. However, like any trade-off, you should evaluate both options to see which works better for your scenario. It isn't necessarily wrong to use a lookup table.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Quick update for future readers: While IANA 2017c established a max length of 14 characters, that applies to individual segments of a zone or link name - not to the entire string. Thus, the advice here is still applicable. – Matt Johnson-Pint Feb 08 '21 at 19:35