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

问题描述

我存储的日期/时间在数据库中作为UTC和计算他们我的应用程序内回基于特定时区本地时间。例如说我有下面的日期/时间：

01/04/2010 00:00

说它是一个国家例如英国这观察DST（夏令时），并在这个特定的时间我们都在夏令时。当我这个日期转换为UTC并将其存储在数据库中它实际上是存储为：

31/03/2010 23:00

随着日期将调整为-1 DST小时。当你在提交的时候观察DST这工作得很好。然而，当时钟被调整回会发生什么？当我拉从数据库的日期，并将其转换为本地时间特定日期时间将被视为 31/03/2009 23:00 而实际上它被加工成 01/04/2010 00:00 。

纠正我，如果我错了，但不是这个有点瑕疵的存储倍UTC时？

时区转换的例子

基本上，我做的是存储日期/时的信息，以便被提交到我的系统的时候让用户做的范围报告。这里是我很存储日期/时间：

 公共的DateTime LocalDateTime（字符串timeZoneId）
{
    VAR TZI = TimeZoneInfo.FindSystemTimeZoneById（timeZoneId）;
    返回TimeZoneInfo.ConvertTimeFromUtc（DateTime.UtcNow，奥兹）.ToUniversalTime（）ToLocalTime（）。
}
 

存储为UTC：

  VAR localDateTime = LocalDateTime（澳大利亚东部标准时间）;
WriteToDB（localDateTime.ToUniversalTime（））;
 

解决方案

您不调整的基础上，无论你的目前的观察他们DST更改的日期 - 你调整根据是否DST观察到的的瞬间你描述的。因此，在一月的情况下，将不适用的调整。

有是的一个问题，但是 - 一些地方的时间不明确。例如，凌晨1:30于2010年10月31日在英国既可以重新present UTC 01:30 UTC或02:30，因为时钟返回从凌晨2点到凌晨1点。你可以从任何的即时获得的UTC中来psented这将在那个瞬间显示本地时间重新$ P $，但操作是不可逆的。

同样它很可能对你有从未出现本地时间 - 凌晨1:30于2010年3月28日并没有在英国发生，例如 - 因为凌晨1点的时钟向前跃升至凌晨2点。

长期和短期的是，如果你想重新present瞬间的时候，你可以使用UTC，并得到一个明确的重新presentation。如果你想重新present在特定的时间段的时间，你需要的时区本身（如欧洲/伦敦），要么即时的UTC再presentation或本地日期和时间与在该特定时间的偏移（以消除歧义周围DST转换）。另一种选择是的只有的存储UTC和从中偏移;这可以让你在那个瞬间告诉当地的时间，但它意味着你不能predict什么地方的时候会是一分钟后，当你真的不知道的时区。 （这是的DateTimeOffset 商店，基本上是这样。）

我们希望让这个比较容易在野田的时间来处理，但你仍然需要要注意的是作为一种可能性。

编辑：

您已经证明的code不正确。这里的原因。我已经改变了code的结构，使其更容易看到的，但你会看到它的执行相同的电话。

  VAR TZI = TimeZoneInfo.FindSystemTimeZoneById（澳大利亚东部标准时间）;
VAR aussieTime = TimeZoneInfo.ConvertTimeFromUtc（DateTime.UtcNow，TZI）;
变种serverLocalTime = aussieTime.ToLocalTime（）;
变种utcTime = serverLocalTime.ToUniversalTime（）;
 

那么，让我们想想现在 - 这是在我的本地时间（UTC + 1，在伦敦），UTC 12:38，22:39在悉尼13:38

您code会给：

  aussieTime = 22:39（正确）
serverLocalTime = 23:39（*不*正确的）
utcTime = 22:39（*不*正确的）
 

您应该的不的通话 ToLocalTime 在 TimeZoneInfo.ConvertTimeFromUtc 的结果 - 它假设它被称为一个UTC 的DateTime （除非它实际上得到DateTimeKind.Local，它不会在这种情况下）。

所以，如果你正确地在这种情况下节省22:39，你的不的精确保存当前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

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

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/2009 23:00 when in reality it was processed as 01/04/2010 00:00.

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

Example of Timezone conversion

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(); 
}

Storing as UTC:

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

解决方案

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.

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.

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.

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.)

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.

EDIT:

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();

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.

Your code will give:

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

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 DateTimeKind.Local, which it won't in this case).

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

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