9

We want to start collecting timezones for each of our addresses in the database. What is the best practice to store the timezones? How would you go about acquiring timezones for the existing address records?

I'm using Microsoft SQL server, .net mvc, C#. Any suggestions would be greatly appreciated.

  • 2
  • Btw a normal time zone gotcha is to use an int, but some time zones can be + or - 30 mins, or even 15 mins :) Oh and remember it's not an exact science, daylight savings and such can throw you off. – Rocklan Apr 26 '13 at 3:26
  • 2
    @LachlanB one way to put it is that the time zone is more a location than it is an time offset. But even that view is not quite enough on its own, e.g. Russia recently changed their policy regarding daylight savings, meaning that you'd have to treat it differently depending on the actual date. – Daniel B Apr 26 '13 at 12:27
  • 1
4

You want to store something that doesn't change all the time (or twice a year). The time zone database https://en.wikipedia.org/wiki/List_of_tz_database_time_zones is exactly the right thing. So you just store two letters.

From this database you can enquire the time offset, and when daylight savings time is active. It is not just the time offset, it also gives you an area, so many places at the same latitude will have different codes and can individually change their time zone rules, and your software will be able to handle it.

The only time when you need to make changes in your database is when a location changes which timezone it belongs to, which would be very rare.

  • Note that the list on wikipedia does not contain 2-character abbreviations for all IANA time zones - plus the mapping is not unambiguous. If you need full historical information, go for the full names instead. – Hulk Aug 28 '17 at 12:23
  • 1
    The only two letter codes I see on the Wikipedia page are country codes. These do not uniquely define timezones. For example, all the timezones in the US have the same two letter country code: US. Similarly, all across Australia have country code AU. – Ian Sep 24 '17 at 6:18
3

The absolute best practice would be to use a database that supports the TIMESTAMP WITH TIMEZONE data type which was defined by SQL99.

SQL Server has a type called datetimeoffset that does everything TIMESTAMP WITH TIMEZONE can do except store the actual time zone. All you can do is store an offset from UTC (e.g., 2013-05-04 12:34:56 -5:00), which means you'll have to determine that before storage based on the time zone.

3

Assuming that you're using .Net framework 4.0 or higher, TimeZoneInfo is what you need.

Store all date values in the database in UTC format. Use either the ID value or the result of ToSerializedString() for each client to create a TimeZoneInfo object to convert stored dates to local format for display.

2

You've said you want to store a time zone for each address in your DB, but you haven't said what you want to do with it. Storing something in a DB is rarely an end in itself.

In any case I would look to use the TZ database, aka Olsen database, either directly or by using some off the shelf software that integrates TZ. You could store a timezone simply as a string to refer to one of the TZ DB entries, e.g. 'Africa/Kampala' or 'Europe/Paris'.

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.