How to store timezone in SQL Server 2005

Question

I am building a web application where users can enter events, including

• event title
• start date/time
• description

The users would like to enter the start date/time including a timezone that corresponds with the location of the event. The events are worldwide so the timezone can change from event to event.

In the SQL Server backend database, I am using datetime for the start date/time. What column type should I use to store the timezone? int? float? decimal?

Answer 1

Since you are using SQL Server 2005, I would recommend storing the time zone as a string in the database, specifically a 32-character string since that is the limit on length for time zone identifiers in the Windows registry.

The values saved should be the values from the TimeZoneInfo ID property (e.g. "Eastern Standard Time") so that you can do calculations in the .NET Framework more easily.

As Joel said, time zones are evil and tricky. Good luck...

Answer 2

If you are using SQL Server 2008, you could use datetimeoffset instead of datetime.

Otherwise, I would use tinyint.

Answer 3

Timezones are tricky, evil things. They're normally stored as a UTC offset, but even that has issues with regards to things like when daylight savings times change over (if at all).

If you're using Sql Server 2008, you can use a datetimeoffset type, which includes the utc offset with the value. Otherwise you'll need two columns.