I want to store a a c# DateTimeOffset value in a SQL Server 2005 database.
我想在SQL Server 2005数据库中存储一个c#DateTimeOffset值。
Sql 2008 has this as a built-in-type, but SQL Server 2005 does not.
Sql 2008将此作为内置类型,但SQL Server 2005没有。
The DateTimeOffset structure has a DateTime value wich I store as DateTime, an an Offset property (of type TimeSpan). Since this is the time zone relative to UTC, presumably it is usally at a whole number of hours or half-hours.
DateTimeOffset结构具有DateTime值,我将其存储为DateTime,一个Offset属性(TimeSpan类型)。由于这是相对于UTC的时区,因此可能通常是整数小时或半小时。
Suggestions on how best to store this in a SQL Server 2005 database?
关于如何最好地将其存储在SQL Server 2005数据库中的建议?
3 个解决方案
#1
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.
使用毫秒作为偏移可能是最灵活的,但我认为分钟更容易阅读。如果您要查看数据库中的“原始”数据,则更容易理解值60 = 1小时而不是3600000.我无法想象您确实需要几分钟的分数作为偏移量。
#2
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.
将所有DateTimeOffsets标准化为公共偏移量,最好是UTC。然后像往常一样存储DateTime。在提取时恢复偏移,该偏移应该是常数。这不会保留原始偏移,但无论如何偏移对时区都是模糊的。
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.
如果您确实需要知道日期/时间来源,那么您需要存储一些时区信息。这是因为简单的偏移不能明确地表示时间的起源。有关选择DateTime,DateTimeOffset和TimeZoneInfo之间的MSDN文档,请参阅(有些令人困惑)。
#3
1
store the datetime as datetime and the offset as milliseconds (bigint)
将datetime存储为datetime,将offset设置为milliseconds(bigint)
#1
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.
使用毫秒作为偏移可能是最灵活的,但我认为分钟更容易阅读。如果您要查看数据库中的“原始”数据,则更容易理解值60 = 1小时而不是3600000.我无法想象您确实需要几分钟的分数作为偏移量。
#2
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.
将所有DateTimeOffsets标准化为公共偏移量,最好是UTC。然后像往常一样存储DateTime。在提取时恢复偏移,该偏移应该是常数。这不会保留原始偏移,但无论如何偏移对时区都是模糊的。
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.
如果您确实需要知道日期/时间来源,那么您需要存储一些时区信息。这是因为简单的偏移不能明确地表示时间的起源。有关选择DateTime,DateTimeOffset和TimeZoneInfo之间的MSDN文档,请参阅(有些令人困惑)。
#3
1
store the datetime as datetime and the offset as milliseconds (bigint)
将datetime存储为datetime,将offset设置为milliseconds(bigint)