Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
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. –  LachlanB Apr 26 '13 at 3:26
    
@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
    

2 Answers 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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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