5

I want to store a a c# DateTimeOffset value in a SQL Server 2005 database.

Sql 2008 has this as a built-in-type, but SQL Server 2005 does not.

The DateTimeOffset structure has a DateTime value which I store as DateTime, an an Offset property (of type TimeSpan). Since this is the time zone relative to UTC, presumably it is usually at a whole number of hours or half-hours.

Suggestions on how best to store this in a SQL Server 2005 database?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Anthony
  • 5,176
  • 6
  • 65
  • 87
  • Related question - http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices – Oded Jul 23 '10 at 20:20

3 Answers3

7

It's not a good idea to assume that an offset is a number of hours or half-hours - there are certainly quarter-hour timezones around.

Using milliseconds for the offset is probably the most flexible, but I'd argue that minutes is a lot easier to read. If you're ever going to look at the "raw" data in the database, it's easier to understand value 60 = 1 hour than 3600000. I can't imagine you really needing fractions of minutes as the offset.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
4

Normalize all DateTimeOffsets to a common offset, preferably UTC. Then just store the DateTime as usual. Upon extraction restore the offset, which should be a constant. This doesn't retain the originating offset but the offset is ambiguous to a timezone anyway.

If you actually need to know the date/time origin, then you'd need to store some timezone information. This is because a simple offset can't unambiguously represent the origin of a time. Please see (the somewhat confusing) MSDN documentation about Choosing Between DateTime, DateTimeOffset, and TimeZoneInfo.

JarrettV
  • 18,845
  • 14
  • 46
  • 43
  • Agreed - store dates as UTC and only worry about timezones and DST offsets at the moment of presentation to the user. – J c Oct 14 '08 at 22:53
  • 1
    I disagree. The DateTimeOffset structure (in C# and SQL 2008) stores time as either UTC or local time + offset. Storing a UTC time + offset would reverse that relationship and only cause confusion. – Robin Minto Nov 08 '08 at 07:30
1

store the datetime as datetime and the offset as milliseconds (bigint)

Manu
  • 28,753
  • 28
  • 75
  • 83