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