存储 DateTime (UTC) 与存储 DateTimeOffset [英] Storing DateTime (UTC) vs. storing DateTimeOffset

问题描述

我通常有一个拦截器"，在从/向数据库读取/写入之前进行 DateTime 转换(从 UTC 到本地时间，从本地时间到 UTC)，所以我可以使用 DateTime.Now(推导和比较)贯穿整个系统，无需担心时区.

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.

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

我应该继续以 UTC 格式存储我的日期 (SQL 2008 - datetime) 还是应该使用 DateTimeOffset (SQL 2008 - datetimeoffset) 存储它?

Should I continue storing my dates (SQL 2008 - datetime) in UTC format or should I instead store it using DateTimeOffset (SQL 2008 - datetimeoffset)?

数据库中的UTC日期(日期时间类型)已经工作并已知这么久了，为什么要改变它?有什么优势?

UTC Dates in the database (datetime type) have been working and known for so long, why change it? What are the advantages?

我已经研究过诸如 这个，但我不是 100% 相信.有什么想法吗?

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

推荐答案

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

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

• 如果你有这样的场景

• If you have a scenario like this

• 一台服务器和多个客户端(所有地理位置在不同的时区)
• 客户使用日期时间信息创建一些数据
• 客户端将其全部存储在中央服务器上

那么:

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

但是:

• UTC 日期时间仅存储 UTC 日期时间，因此您没有数据来源客户端位置的本地时间信息
• 其他客户端不知道日期时间信息来自何处的本地时间
• 其他客户端只能从数据库(使用 UTC 时间)计算他们的本地时间，而不是数据来源的客户端本地时间

• 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

简单的例子是机票预订系统......机票应该包含2次:- 起飞"时间(在来自"城市的时区)- 登陆"时间(在目的地"城市的时区)

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)

这篇关于存储 DateTime (UTC) 与存储 DateTimeOffset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！