Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones?

Which is best ?

  • Use the SQL DateTime data type and insert date/time as UTC (e.g. convert local time to UTC in application business layer and pass to DB already in UTC format)
  • Use the SQL DateTimeOffset data type which will record the offset relative to UTC?
share|improve this question
add comment

1 Answer

Use the DateTimeOffset datatype. Each server would store the data locally in its own timezone, storing the offset from UTC as well. When data is gathered to a central area, you can easily convert to UTC for comparison purposes.

share|improve this answer
    
When using DateTimeOffset is there any need to have other tables storing timezones and daylight saving times or is it enough to have the local time with offset from UTC for any date calculations that may be required? see here for example. –  Matt F Dec 6 '11 at 9:41
    
Can DateTimeOffset be used when servers will be hosted in a single timezone? Or is it best to use DateTime and store all as UTC and lookup offset for displaying on UI? –  Matt F Dec 8 '11 at 10:31
add comment

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.