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

问题描述

我将数据库中的日期/时间作为UTC存储，并根据具体的时区在本地时间内将其计算回本地。例如，我有以下日期/时间：

01/04/2010 00:00

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

31/03/2010 23:00

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

如果我错了，请更正我，但不是这样存储时间为UTC时的缺陷？

时区转换示例

我正在做的是将信息提交到我的系统的日期/时间，以允许用户执行范围报告。这是我如何存储日期/时间：

  public DateTime LocalDateTime（string timeZoneId）
 {
 var tzi = TimeZoneInfo.FindSystemTimeZoneById（timeZoneId）; 
返回TimeZoneInfo.ConvertTimeFromUtc（DateTime.UtcNow，tzi）.ToUniversalTime（）。ToLocalTime（）; 
} 
  

以UTC存储：

  var localDateTime = LocalDateTime（AUS Eastern Standard Time）; 
 WriteToDB（localDateTime.ToUniversalTime（））; 
  

解决方案

您不调整DST更改的日期关于您是否正在观察他们 - 您可以根据在描述时刻观察DST是否进行调整。。所以在1月的情况下，你不会应用调整。

然而，是一个问题，但是一些本地时代是不明确的。例如，2010年10月31日上午1:30在英国可以代表UTC 01:30或UTC 02:30，因为钟表从上午2点到凌晨1点。您可以从以UTC表示的任何即时获得在当时时间显示的当地时间，但操作不可逆。

同样地，你可能有一个从来不会发生的当地时间 - 例如2010年3月28日上午1:30在英国没有发生，因为在凌晨1点，时钟跳到凌晨2点。

长而短的是，如果你想要及时表达一个瞬间，你可以使用UTC并得到一个明确的表示。如果您想要在特定时区表示时间，则需要时区本身（例如欧洲/伦敦）以及即时的UTC表示或当地的日期和时间以及该特定时间的偏移量（消除DST转换周围的歧义）。另一个选择是仅限于存储UTC及其偏移量;这可以让您在当时的时间告诉当地时间，但这意味着您无法预测当地时间将在一分钟之后，因为您不太了解时区。 （这是什么 DateTimeOffset 存储，基本上。）

我们希望使这个相当容易处理 Noda时间，但您仍然需要注意到这一点。

编辑：

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

  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 UTC，22:39在悉尼。

你的代码将给出：

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

您应该不调用 ToLocalTime on TimeZoneInfo.ConvertTimeFromUtc 的结果 - 它将假定它正在UTC DateTime （除非它是实际上得到了DateTimeKind.Local，在这种情况下不会这样）。

所以如果你在这种情况下准确地保存22:39， 't 准确地保存当前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屋！