存储 DateTime (UTC)与存储 DateTimeOffset

我通常有一个“拦截器”，在从/向数据库读/写之前进行 DateTime 转换(从 UTC 到本地时间，从本地时间到 UTC) ，所以我可以在整个系统中使用 DateTime.Now(派生和比较) ，而不用担心时区问题。

关于序列化和在计算机之间移动数据，不必麻烦，因为日期时间总是 UTC。

我应该继续使用 UTC 格式存储日期(SQL 2008-datetime) ，还是应该使用 DateTimeOffset(SQL 2008-datetime 偏移量)存储日期？

UTC 数据库中的日期(日期时间类型)已经工作并且已经知道这么长时间了，为什么要更改它？有什么好处？

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

78423 次浏览
小开

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:

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.

小开

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

  • If you have a scenario like this

    • One server and 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 offset to 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

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)

小开

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.


提交答案