19

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.

ozz
  • 8,322
  • 2
  • 29
  • 62
Rodney Hickman
  • 589
  • 3
  • 5
  • 9
  • 2
    http://stackoverflow.com/questions/3446906/how-to-store-timezoneinfo-objects-in-a-database – Robert Harvey Apr 25 '13 at 21:28
  • 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 03: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
    http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices – cottsak Jul 24 '13 at 02:20

4 Answers4

10

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.

gnasher729
  • 42,090
  • 4
  • 59
  • 119
  • 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
  • 3
    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 06:18
  • Use the Olson names, eg America/New_York or America/Costa_Rica – OsamaBinLogin Oct 27 '21 at 20:11
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.

Peregrine
  • 1,236
  • 1
  • 7
  • 9
2

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.

Blrfl
  • 20,235
  • 2
  • 49
  • 75
  • AND whether it's DST; for instance, pacific timezone is offset -700 or -800 depending on what time of the year. An offset for a specific time is OK, but not for use throughout the year - your stored times will slosh back and forth by an hour. Also, DST switches over at different times in different timezones - US and Europe are totally different. And, south of the equator, DST works backwards. Don't write this code yourself - use a library or SQL. – OsamaBinLogin Oct 27 '21 at 20:20
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'.

bdsl
  • 2,365
  • 1
  • 14
  • 14