存储 DateTime (UTC) 与存储 DateTimeOffset
我通常有一个“拦截器”，在从数据库读取/写入之前进行 DateTime 转换（从 UTC 到本地时间，从本地时间到 UTC），所以我可以使用 DateTime.Now （推导和比较）整个系统，无需担心时区。
关于序列化和在计算机之间移动数据，无需费心，因为日期时间始终是 UTC。
我应该继续以 UTC 格式存储日期（SQL 2008 - datetime），还是应该使用 DateTimeOffset （SQL 2008 - datetimeoffset）存储它？
数据库中的 UTC 日期（日期时间类型）已经工作并已知这么长时间了，为什么要更改它？有什么优点？
我已经研究过诸如 这个，但我并不是 100% 相信。有什么想法吗？
I usually have an "interceptor" that right before reading/writing from/to the database does DateTime conversion (from UTC to local time, and from local time to UTC), so I can use DateTime.Now (derivations and comparisions) throughout the system without worrying about time zones.
Regarding serialization and moving data between computers, there is no need to bother, as the datetime is always UTC.
Should I continue storing my dates (SQL 2008 - datetime) in UTC format or should I instead store it using DateTimeOffset (SQL 2008 - datetimeoffset)?
UTC Dates in the database (datetime type) have been working and known for so long, why change it? What are the advantages?
I have already looked into articles like this one, but I'm not 100% convinced though. Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
有一个巨大的区别，即您不能单独使用 UTC。
如果你有这样的场景
然后：
但是：
简单的例子是机票预订系统...机票应该包含2次：
- “起飞”时间（以“出发”城市的时区为准）
- “着陆”时间（以“目的地”城市的时区为准）
There is one huge difference, where you cannot use UTC alone.
If you have a scenario like this
Then:
But:
Simple example is flight ticket reservation system ... Flight ticket should contain 2 times:
- "take off" time (in timezone of "From" city)
- "landing" time (in timezone of "Destination" city)
对于所有历史时间（即记录发生的事件），您使用 UTC 是绝对正确的。始终可以从 UTC 转换为本地时间，但并非总是相反。
什么时候使用当地时间？回答这个问题：
如果答案为“是”，则仅存储当地时间。显然，这仅适用于未来的日期，并且通常仅适用于以某种方式影响人们的日期。
为什么要存储时区/偏移量？
首先，如果您想记录执行该操作的用户的偏移量，您可能最好这样做，即在登录时记录该用户的位置和时区。
其次，如果你想转换为显示，你需要有一个该时区的所有本地时间偏移转换的表，仅仅知道当前偏移是不够的，因为如果你显示六个月前的日期/时间，偏移将有所不同。
You are absolutely correct to use UTC for all historical times (i.e. recording events happened). It is always possible to go from UTC to local time but not always the other way about.
When to use local time? Answer this question:
Only store local time if the answer is "yes". Obviously that will only be for future dates, and usually only for dates that affect people in some way.
Why store a time zone/offset?
Firstly, if you want to record what the offset was for the user who carried out the action, you would probably be best just doing that, i.e. at login record the location and timezone for that user.
Secondly if you want to convert for display, you need to have a table of all local time offset transitions for that timezone, simply knowing the current offset is not enough, because if you are showing a date/time from six months ago the offset will be different.
DATETIMEOFFSET 使您能够将本地时间和 UTC 时间存储在一个字段中。
这允许以本地或 UTC 时间进行非常简单且高效的报告，而无需以任何方式处理数据以进行显示。
这是两个最常见的要求 - 本地报告的当地时间和团体报告的 UTC 时间。
本地时间存储在 DATETIMEOFFSET 的 DATETIME 部分中，UTC 的 OFFSET 存储在 OFFSET 部分中，因此转换很简单，并且由于不需要知道数据来自的时区，因此都可以在数据库级别完成。
如果您不需要将时间精确到毫秒（例如仅分钟或秒），则可以使用 DATETIMEOFFSET(0)。这样，DATETIMEOFFSET 字段将只需要 8 个字节的存储空间 - 与 DATETIME 相同。
因此，使用 DATETIMEOFFSET 而不是 UTC DATETIME 可以为报告提供更大的灵活性、效率和简单性。
A DATETIMEOFFSET gives you the ability to store local time and UTC time in one field.
This allows for very simple and efficient reporting in local or UTC time without the need to process the data for display in any way.
These are the two most common requirements - local time for local reports and UTC time for group reports.
The local time is stored in the DATETIME portion of the DATETIMEOFFSET and the OFFSET from UTC is stored in the OFFSET portion, thus conversion is simple and, since it requires no knowledge of the timezone the data came from, can all be done at database level.
If you don't require times down to milliseconds, e.g. just to minutes or seconds, you can use DATETIMEOFFSET(0). The DATETIMEOFFSET field will then only require 8 bytes of storage - the same as a DATETIME.
Using a DATETIMEOFFSET rather than a UTC DATETIME therefore gives more flexibility, efficiency and simplicity for reporting.