21

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.

4
  • 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, 2013 at 3:26
  • 5
    @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, 2013 at 12:27
  • 1

5 Answers 5

12

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.

4
  • 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, 2017 at 12:23
  • 6
    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, 2017 at 6:18
  • 3
    Use the Olson names, eg America/New_York or America/Costa_Rica Oct 27, 2021 at 20:11
  • There are some oddities regarding Arizona, which infamously does not use daylight saving time, EXCEPT for the Navajo Nation reservation. In this case you may have to include zip codes. Unless you judge it does not matter.
    – NicVerAZ
    Nov 27, 2023 at 21:09
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

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.

1
  • 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. Oct 27, 2021 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'.

-3

You can get the data directly from SQL Server like this:

SELECT * FROM sys.time_zone_info

This will give you a list of Timezones (141 at this time). This is a sample:

name    current_utc_offset  is_currently_dst
Dateline Standard Time  -12:00  0
UTC-11                  -11:00  0
Aleutian Standard Time  -10:00  0
Hawaiian Standard Time  -10:00  0
Marquesas Standard Time -09:30  0
Alaskan Standard Time   -09:00  0
3
  • 1
    That’s the time zone of your server. Not the time zone of customers from London to new Zealand.
    – gnasher729
    Nov 27, 2023 at 18:26
  • Are you sure? Have you tried it?
    – Greg Gum
    Nov 27, 2023 at 20:16
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Nov 27, 2023 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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