SQL 存储 DateTime (UTC) 与存储 DateTimeOffset

Storing DateTime (UTC) vs. storing DateTimeOffset

提问by Frederico

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.

我通常有一个“拦截器”，在从/向数据库读取/写入之前进行 DateTime 转换（从 UTC 到本地时间，从本地时间到 UTC），所以我可以DateTime.Now在整个系统中使用（推导和比较）而不用担心关于时区。

Regarding serialization and moving data between computers, there is no need to bother, as the datetime is always UTC.

关于计算机之间的序列化和移动数据，无需费心，因为日期时间始终是 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 格式存储我的日期 (SQL 2008 - datetime) 还是应该使用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?

数据库中的UTC日期（日期时间类型）已经工作并已知这么久了，为什么要更改它？有哪些优势？

I have already looked into articles like this one, but I'm not 100% convinced though. Any thoughts?

我已经看过像这样的文章，但我不是 100% 相信。有什么想法吗？

回答by Marcel Toth

There is one huge difference, where you cannot use UTC alone.

有一个巨大的区别，您不能单独使用 UTC。

• If you have a scenario like this

  • One serverand several clients(all geographically in different timezones)
  • Clients create some data with datetime information
  • Clients store it all on central server

• Then:

  • datetimeoffset stores Local time of the client and ALSO offsetto the UTC time
  • all clients know UTC time of all data and also a local time in the place where the information originated

• But:

  • UTC datetime stores just UTC datetime, so you do not have information about local time in the client location where data originated
  • Other clients do not know the local time of the place, where datetime information came from
  • Other clients can only calculate their local time from the database (using UTC time) not the local time of the client, where the data originated

• 如果你有这样的场景

  • 一台服务器和几个客户端（都在不同的时区）
  • 客户端使用日期时间信息创建一些数据
  • 客户端将其全部存储在中央服务器上

• 然后：

  • datetimeoffset 存储客户端的本地时间和UTC 时间的偏移量
  • 所有客户端都知道所有数据的 UTC 时间以及信息来源地的本地时间

• 但：

  • UTC 日期时间仅存储 UTC 日期时间，因此您在数据来源的客户端位置没有有关本地时间的信息
  • 其他客户端不知道该地点的当地时间，日期时间信息来自哪里
  • 其他客户端只能从数据库中计算他们的本地时间（使用 UTC 时间），而不是数据来源的客户端本地时间

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)

简单的例子是机票预订系统......机票应该包含2次：-“起飞”时间（“出发”城市的时区）-“着陆”时间（“目的地”城市的时区）

回答by Ben

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.

对于所有历史时间（即记录发生的事件）使用 UTC 是绝对正确的。总是可以从 UTC 到本地时间，但并非总是相反。

When to use local time? Answer this question:

什么时候使用当地时间？回答这个问题：

If the government suddenly decide to change daylight savings, would you like this data to change with it?

如果政府突然决定改变夏令时，您希望这些数据随之改变吗？

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.

其次，如果你想转换显示，你需要有一个该时区所有本地时间偏移转换的表，仅仅知道当前偏移是不够的，因为如果你显示六个月前的日期/时间，偏移将与众不同。

回答by PapillonUK

A DATETIMEOFFSET gives you the ability to store local time and UTC time in one field.

DATETIMEOFFSET 使您能够在一个字段中存储本地时间和 UTC 时间。

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.

这允许在本地或 UTC 时间进行非常简单和有效的报告，而无需以任何方式处理数据以进行显示。

These are the two most common requirements - local time for local reports and UTC time for group reports.

这是两个最常见的要求 - 本地报告的本地时间和组报告的 UTC 时间。

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.

本地时间存储在 DATETIMEOFFSET 的 DATETIME 部分中，UTC 的偏移量存储在 OFFSET 部分中，因此转换很简单，并且由于它不需要知道数据来自的时区，因此可以在数据库级别完成.

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.

如果您不需要小到毫秒的时间，例如只需几分钟或几秒，您可以使用 DATETIMEOFFSET(0)。DATETIMEOFFSET 字段将只需要 8 个字节的存储空间 - 与 DATETIME 相同。

Using a DATETIMEOFFSET rather than a UTC DATETIME therefore gives more flexibility, efficiency and simplicity for reporting.

因此，使用 DATETIMEOFFSET 而不是 UTC DATETIME 为报告提供了更大的灵活性、效率和简单性。