What should I map a TimeZoneInfo property to a SQL Server 2008 DB Type?

Question

Hi folks,

I have a TimeZoneInfo property on some object which I need to save to a Microsoft SQL Server 2008 database. What type of database field type should I use, please?

If it helps, I'm also using Entity Framework 4, which came with my Visual Studio 2010.

Answer 1

You should use the ID of the TimeZoneInfo - as then you can retrieve the original zone again with TimeZoneInfo.FindSystemTimeZoneById.

Note that while storing dates and times as a DateTimeOffset is a valid alternative in some situations, it isn't sufficient for all. For example, suppose you want to store the information that you have a 3pm meeting every week. Storing a single instance of that as a DateTimeOffset won't tell you when the meeting is next week - because you won't know whether daylight saving time has changed. (In that situation you'd probably want to store the local time of day, the fact that it's a weekly meeting, and the day of the week. Recurrence rules get complicated fast, unfortunately.)