将日期/时间作为 UTC 存储在数据库中 [英] Storing date/times as UTC in database

问题描述

我将日期/时间作为 UTC 存储在数据库中，并在我的应用程序中根据特定时区将它们计算回本地时间.例如，我有以下日期/时间:

I am storing date/times in the database as UTC and computing them inside my application back to local time based on the specific timezone. Say for example I have the following date/time:

01/04/2010 00:00

说它是针对一个国家/地区的，例如英国遵守 DST(夏令时)，在这个特定时间我们处于夏令时.当我将此日期转换为 UTC 并将其存储在数据库中时，它实际上存储为:

Say it is for a country e.g. UK which observes DST (Daylight Savings Time) and at this particular time we are in daylight savings. When I convert this date to UTC and store it in the database it is actually stored as:

31/03/2010 23:00

因为 DST 的日期将调整为 -1 小时.当您在提交时观察 DST 时，这可以正常工作.但是，当时钟调回时会发生什么?当我从数据库中提取该日期并将其转换为本地时间时，该特定日期时间将被视为 31/03/2010 23:00 而实际上它被处理为 01/04/2010 00:00.

As the date would be adjusted -1 hours for DST. This works fine when your observing DST at time of submission. However, what happens when the clock is adjusted back? When I pull that date from the database and convert it to local time that particular datetime would be seen as 31/03/2010 23:00 when in reality it was processed as 01/04/2010 00:00.

如果我错了，请纠正我，但在将时间存储为 UTC 时这不是一个缺陷吗?

Correct me if I am wrong but isn't this a bit of a flaw when storing times as UTC?

时区转换示例

基本上我正在做的是存储信息提交到我的系统的日期/时间，以便允许用户进行范围报告.这是我存储日期/时间的方式:

Basically what I am doing is storing the date/times of when information is being submitted to my system in order to allow users to do a range report. Here is how I am storing the date/times:

public DateTime LocalDateTime(string timeZoneId)
{
    var tzi = TimeZoneInfo.FindSystemTimeZoneById(timeZoneId);
    return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tzi).ToUniversalTime().ToLocalTime(); 
}

存储为 UTC:

var localDateTime = LocalDateTime("AUS Eastern Standard Time");
WriteToDB(localDateTime.ToUniversalTime());

推荐答案

您不会根据您是否当前观察 DST 更改的日期 - 您可以根据是否在您描述的那一刻观察到 DST.因此，在 1 月份的情况下，您不会应用调整.

You don't adjust the date for DST changes based on whether you're currently observing them - you adjust it based on whether DST is observed at the instant you're describing. So in the case of January, you wouldn't apply the adjustment.

是一个问题，但是 - 一些当地时间是模棱两可的.例如，英国 2010 年 10 月 31 日凌晨 1:30 可以表示 UTC 01:30 或 UTC 02:30，因为时钟从凌晨 2 点回到凌晨 1 点.您可以从以 UTC 表示的任何 即时 到将在该时刻显示的本地时间，但该操作是不可逆的.

There is a problem, however - some local times are ambiguous. For example, 1:30am on October 31st 2010 in the UK can either represent UTC 01:30 or UTC 02:30, because the clocks go back from 2am to 1am. You can get from any instant represented in UTC to the local time which would be displayed at that instant, but the operation isn't reversible.

同样，您很可能有一个永远不会出现的当地时间 - 例如，2010 年 3 月 28 日凌晨 1:30 在英国没有发生 - 因为在凌晨 1 点时钟跳到凌晨 2 点.

Likewise it's very possible for you to have a local time which never occurs - 1:30am on March 28th 2010 didn't happen in the UK, for example - because at 1am the clocks jumped forward to 2am.

总而言之，如果您想表示一个瞬间，您可以使用 UTC 并获得一个明确的表示.如果您尝试表示特定时区的时间，则需要时区本身(例如欧洲/伦敦)以及即时的 UTC 表示或本地日期和时间以及该特定时间的偏移量(以消除 DST 转换的歧义).另一种选择是仅存储UTC和它的偏移量；这使您可以在那一刻告诉当地时间，但这意味着您无法预测一分钟后的当地时间，因为您并不真正知道时区.(基本上，这就是 DateTimeOffset 存储的内容.)

The long and the short of it is that if you're trying to represent an instant in time, you can use UTC and get an unambiguous representation. If you're trying to represent a time in a particular time zone, you'll need the time zone itself (e.g. Europe/London) and either the UTC representation of the instant or the local date and time with the offset at that particular time (to disambiguate around DST transitions). Another alternative is to only store UTC and the offset from it; that allows you to tell the local time at that instant, but it means you can't predict what the local time would be a minute later, as you don't really know the time zone. (This is what DateTimeOffset stores, basically.)

我们希望在 Noda Time 内让这件事变得相当容易处理，但您仍然需要意识到这是一种可能性.

We're hoping to make this reasonably easy to handle in Noda Time, but you'll still need to be aware of it as a possibility.

您显示的代码不正确.这就是为什么.我更改了代码的结构以使其更易于查看，但您会看到它正在执行相同的调用.

The code you've shown is incorrect. Here's why. I've changed the structure of the code to make it easier to see, but you'll see it's performing the same calls.

var tzi = TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time");
var aussieTime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tzi);
var serverLocalTime = aussieTime.ToLocalTime(); 
var utcTime = serverLocalTime.ToUniversalTime();

所以，让我们现在想想 - 我的当地时间是 13:38(伦敦时间 UTC+1)，世界标准时间 12:38，悉尼 22:39.

So, let's think about right now - which is 13:38 in my local time (UTC+1, in London), 12:38 UTC, 22:39 in Sydney.

您的代码将给出:

aussieTime = 22:39 (correct)
serverLocalTime = 23:39 (*not* correct)
utcTime = 22:39 (*not* correct)

您应该不在 TimeZoneInfo.ConvertTimeFromUtc 的结果上调用 ToLocalTime - 它会假定它是在 UTC 上调用的DateTime(除非它实际上有一种 DateTimeKind.Local，在这种情况下它不会).

You should not call ToLocalTime on the result of TimeZoneInfo.ConvertTimeFromUtc - it will assume that it's being called on a UTC DateTime (unless it's actually got a kind of DateTimeKind.Local, which it won't in this case).

因此，如果您在这种情况下准确地保存了 22:39，那么您并没有准确地保存 UTC 中的当前时间.

So if you're accurately saving 22:39 in this case, you aren't accurately saving the current time in UTC.

这篇关于将日期/时间作为 UTC 存储在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！