Search code examples
c#sql-servertimezonenodatimeiana

Storing Time Zones on an SQL Server

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.

Solution

  • 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.