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.
我通常有一个“拦截器”，它在从/写到数据库之前进行时间转换(从UTC到本地时间，从本地时间到UTC)，因此我可以使用DateTime。现在(推导和比较)在整个系统中不用担心时区。
Regarding serialization and moving data between computers, there is no need to bother, as the datetime is always UTC.
对于串行化和在计算机之间移动数据，没有必要麻烦，因为datetime始终是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日期(datetime类型)已经工作了很长时间，为什么要更改它呢?优势是什么?
I have already looked into articles like this one, but I'm not 100% convinced though. Any thoughts?
我已经看过像这样的文章，但我不是百分之百地相信。任何想法吗?
3 个解决方案
#1
98
There is one huge difference, where you cannot use UTC alone.
有一个巨大的区别，你不能单独使用UTC。
-
If you have scenario like this
如果你有这样的场景
- One server and several clients (all geographically in different timezones)
- 一个服务器和几个客户端(都位于不同的时区)
- Clients create some data with datetime information
- 客户端使用datetime信息创建一些数据
- Clients store it all on central server
- 客户端将其全部存储在*服务器上
-
Then:
然后:
- datetimeoffset stores UTC time and ALSO offset to the local time of the client
- datetimeoffset存储UTC时间，也存储客户端的本地时间
- all clients know UTC time of all data and also local time in the place where information originated
- 所有客户端都知道所有数据的UTC时间，也知道信息来源的本地时间
-
But:
但是:
- UTC datetime stores just UTC datetime, so you do not have information about local time in the client location where data originated
- UTC datetime只存储UTC datetime，因此在数据起源的客户端位置没有关于本地时间的信息
- Other clients do not know the local time of the place, where datetime information came from
- 其他客户不知道本地时间，datetime信息来自于何处
- 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
- 其他客户机只能从数据库(使用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次:-“起飞”时间(在“从”城市)-“降落”时间(在“目的地”城市的时区)
#2
17
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的偏移量存储在偏移量部分，因此转换非常简单，而且由于不需要知道数据来自哪个时区，所以可以在数据库级别上完成。
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可以为报表提供更多的灵活性、效率和简便性。
#3
17
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.
其次，如果您想要转换为display，您需要有一个表，其中包含该时区的所有本地时间偏移转换，仅仅知道当前偏移量是不够的，因为如果您显示6个月前的日期/时间，那么偏移量将是不同的。
#1
98
There is one huge difference, where you cannot use UTC alone.
有一个巨大的区别，你不能单独使用UTC。
-
If you have scenario like this
如果你有这样的场景
- One server and several clients (all geographically in different timezones)
- 一个服务器和几个客户端(都位于不同的时区)
- Clients create some data with datetime information
- 客户端使用datetime信息创建一些数据
- Clients store it all on central server
- 客户端将其全部存储在*服务器上
-
Then:
然后:
- datetimeoffset stores UTC time and ALSO offset to the local time of the client
- datetimeoffset存储UTC时间，也存储客户端的本地时间
- all clients know UTC time of all data and also local time in the place where information originated
- 所有客户端都知道所有数据的UTC时间，也知道信息来源的本地时间
-
But:
但是:
- UTC datetime stores just UTC datetime, so you do not have information about local time in the client location where data originated
- UTC datetime只存储UTC datetime，因此在数据起源的客户端位置没有关于本地时间的信息
- Other clients do not know the local time of the place, where datetime information came from
- 其他客户不知道本地时间，datetime信息来自于何处
- 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
- 其他客户机只能从数据库(使用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次:-“起飞”时间(在“从”城市)-“降落”时间(在“目的地”城市的时区)
#2
17
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的偏移量存储在偏移量部分，因此转换非常简单，而且由于不需要知道数据来自哪个时区，所以可以在数据库级别上完成。
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可以为报表提供更多的灵活性、效率和简便性。
#3
17
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.
其次，如果您想要转换为display，您需要有一个表，其中包含该时区的所有本地时间偏移转换，仅仅知道当前偏移量是不够的，因为如果您显示6个月前的日期/时间，那么偏移量将是不同的。