Menu
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?
812
It's best not to store your air conditioner in an outdoor garage or anywhere else that might be subject to freezing temperatures. Outdoor storage areas can also be vulnerable to pests such as rodents and insects, which can chew through the box and get dirt, bacteria and feces inside of the air conditioning unit.
Effects of Using AC Sideways When you lay your portable air conditioner on its side or tilt it at an angle, certain parts of the compressor are deprived of lubrication oil. This causes the compressor's components to collide, resulting in wear and tear.
If an AC unit isn't stored correctly, it can suffer irreparable damage. For example, if a unit is stored outside or in a cold environment, the coils can freeze, rendering the unit inoperable.
Avoid putting your A/C unit in the garage or a backyard shed as critters might make it a home during the winter, and mice and other rodents tend to chew on the wires and insulation. Instead, place it in your home's basement, attic or utility room.
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.
103
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.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
