ADO.NET + SQL Server DateTime列的生命周期中如何处理时区？ [英] How is timezone handled in the lifecycle of an ADO.NET + SQL Server DateTime column?

问题描述

使用SQL Server 2008.这是一个非常重要的问题，我可以真正使用一些精心设计的信息，但是Google上的信息似乎是围绕这个主题跳舞的，如果有一些详细的阐述，这个工作...

假设我有一个datetime列，在ADO.NET中我将其设置为DateTime.UtcNow。

1）SQL Server是否相应地存储DateTime.UtcNow，或者根据服务器安装位置的时区再次偏移 然后在查询时返回偏移反转？我想我知道这个答案是当然可以存储它而不会再次抵消它，但是要确定。

所以我查询它并将其从对象到DateTime后，例如IDataReader列。据我所知，System.DateTime具有内部跟踪是否为UTC DateTime的元数据，或者是偏移的DateTime，这可能或可能不会导致.ToLocalTime（）和.ToUniversalTime（）根据此状态具有不同的行为。那么，这个转换的System.DateTime对象已经知道它是UTC DateTime实例，还是假定它已经被偏移了？

---

现在，我们使用UtcNow，当执行ADO.NET INSERT或UPDATE时，我使用DateTime.Now。 3）ADO.NET是否将偏移量传递给SQL Server，并且SQL Server存储DateTime.Now与偏移元数据？

所以我查询它，并将其从IDataReader列转换为DateTime。

4）这个被抛出的System.DateTime对象是否已经知道这是一个偏移时间，还是假定它是UTC？

解决方案

执行一些单元测试来回答我自己的问题所有四个部分。

1：SQL Server是否相应地存储DateTime.UtcNow，或者根据服务器安装位置的时区再次偏移，然后返回在查询时偏移反转？

执行此）：

  cmd.CommandText =INSERT INTO testtbl（val）VALUES（@newval） ; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.UtcNow））; 
 cmd.ExecuteNonQuery（）; 
 cmd.CommandText =SELECT CAST（val as varchar）value FROM testtbl; 
 Console.WriteLine（cmd.ExecuteScalar（））; 
  

这是在当地时间下午1点30分（UTC时间为7时或8时30分）的结果）是：

  2010年6月3日8:30 PM 
  

然后我尝试过：

  cmd.CommandText =INSERT INTO testtbl val）VALUES（@newval）; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.UtcNow））; 
 cmd.ExecuteNonQuery（）; 
 Console.WriteLine（将时区更改为utc）; 
 Console.ReadLine（）; 
 cmd.CommandText =SELECT CAST（val as varchar）value FROM testtbl; 
 Console.WriteLine（cmd.ExecuteScalar（））; 
 Console.WriteLine（更改时区返回本地）; 
  

在UTC下午9:25执行，它返回

  2010年6月3日9:25 PM 
  

到DateTime.Now：

  cmd.CommandText =INSERT INTO testtbl（val）VALUES（@newval）; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.Now））; 
 cmd.ExecuteNonQuery（）; 
 Console.WriteLine（将时区更改为utc）; 
 Console.ReadLine（）; 
 cmd.CommandText =SELECT CAST（val as varchar）value FROM testtbl; 
 Console.WriteLine（cmd.ExecuteScalar（））; 
 Console.WriteLine（更改时区返回本地）; 
  

在3:55 PM（本地; -7h）执行，返回：

  2010年6月3日3:55 PM 
  

2：所以我查询它，并将它从对象转换为DateTime，从IDataReader列获取后。这个被抛出的System.DateTime对象已经知道它是UTC DateTime实例，还是假定它已经被偏移了？

$ b

  cmd.CommandText =INSERT INTO testtbl（val）VALUES（@newval）; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.UtcNow））; 
 cmd.ExecuteNonQuery（）; 
 cmd.CommandText =SELECT val value FROM testtbl; 
 var retval =（DateTime）cmd.ExecuteScalar（）; 
 Console.WriteLine（Kind：+ retval.Kind）; 
 Console.WriteLine（UTC：+ retval.ToUniversalTime（）。ToString（））; 
 Console.WriteLine（Local：+ retval.ToLocalTime（）。ToString（））; 
  

此结果（在当地时间下午1:58执行）为：

 种类：未指定
 UTC：6/4/2010 3:58:42 AM 
本地：6/3/2010 1:58:42 PM 
  

那就是 .ToUniversalTime（） code>最终从本地时间抵消到UTC时间，而不是一次，而是两次（??），而$ code> .ToLocalTime（）最终没有抵消。 3，ADO.NET将偏移量传递给SQL Server，SQL Server是否存储DateTime.Now与偏移量元数据？

在不执行任何单元测试的情况下，答案已知是仅使用DateTimeOffsetSQL类型。 SQL的 datetime 不做偏移。

4：这个被抛出的System.DateTime对象是否已经知道它是偏移时间，还是假定它是UTC？

SQL的DateTimeOffset类型返回为.NET DateTimeOffset结构体。

以下在本地时间3:31执行的列为 offval 是datetimeoffset SQL类型，

  cmd.CommandText =INSERT INTO testtbl（offval）VALUES（@newval）; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.Now））; 
 cmd.ExecuteNonQuery（）; 
 cmd.CommandText =SELECT offval value FROM testtbl; 
 object retvalobj = cmd.ExecuteScalar（）; 
 Console.WriteLine（Type：+ retvalobj.GetType（）。Name）; 
 var retval =（DateTimeOffset）retvalobj; 
 Console.WriteLine（ToString（）：+ retval.ToString（））; 
 Console.WriteLine（UTC：+ retval.ToUniversalTime（）。ToString（））; 
 Console.WriteLine（Local：+ retval.ToLocalTime（）。ToString（））; 
  

这导致：

 类型：DateTimeOffset 
 ToString（）：6/3/2010 3:31:47 PM +00：00 
 UTC：6/3/2010 3:31:47 PM +00：00 
本地：6/3/2010 8:31:47 AM -07：00 
  

令人惊讶的差距。

---

使用DateTime.Now而不是DateTime.UtcNow返回并执行上述问题＃1的测试，我验证了在存储到数据库之前，ADO.NET不会转换为通用时间。

这是在当地时间（-7h）下午3:27执行：

  cmd.CommandText =INSERT INTO testtbl（val）VALUES（@newval）; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.Now））; 
 cmd.ExecuteNonQuery（）; 
 Console.WriteLine（将时区更改为utc）; 
 Console.ReadLine（）; 
 cmd.CommandText =SELECT CAST（val as varchar）value FROM testtbl; 
 Console.WriteLine（cmd.ExecuteScalar（））; 
 Console.WriteLine（更改时区返回本地）; 
  

..返回..

  2010年6月3日3:27 PM 
  

在3:17 PM执行当地时间：

  cmd.CommandText =INSERT INTO testtbl（val）VALUES（@newval）; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.UtcNow））; 
 cmd.ExecuteNonQuery（）; 
 cmd.CommandText =SELECT val FROM testtbl; 
 var result =（DateTime）cmd.ExecuteScalar（）; 
 Console.WriteLine（Kind：+ result.Kind）; 
 Console.WriteLine（ToString（）：+ result.ToString（））; 
 Console.WriteLine（添加1分钟，大于UtcNow？
 +（result.AddMinutes（1）> DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（添加1分钟，大于现在
 +（result.AddMinutes（1）> DateTime.Now）.ToString（））; 
 Console.WriteLine（Add 1 minute，than UtcNow？
 +（result.AddMinutes（1）< DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（Add 1 minutes，less than Now？
 +（result.AddMinutes（1）< DateTime.Now）.ToString（））; 
 Console.WriteLine（Subtract 1 minute，than UtcNow？
 +（result.AddMinutes（-1）> DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（减去1分钟，大于现在
 +（result.AddMinutes（-1）> DateTime.Now）.ToString（））; 
 Console.WriteLine（Subtract 1 minute，than than UtcNow？
 +（result.AddMinutes（-1）< DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（Subtract 1 minute，less than Now？
 +（result.AddMinutes（-1）< DateTime.Now）.ToString（））; 
  

导致：

 种类：未指定
 ToString（）：6/3/2010 10:17:05 PM 
添加1分钟，大于UtcNow？ True 
添加1分钟，大于现在？ True 
添加1分钟，少于UtcNow？ False 
加1分钟，小于现在？ False 
减去1分钟，大于UtcNow？假
减1分，大于现在？ True 
减去1分钟，小于UtcNow？真
减1分，小于现在？ False 
  

将此与DateTime进行比较。现在：

  cmd.CommandText =INSERT INTO testtbl（val）VALUES（@newval）; 
 cmd.Parameters.Add（new SqlParameter（@ newval，DateTime.Now））; 
 cmd.ExecuteNonQuery（）; 
 cmd.CommandText =SELECT val FROM testtbl; 
 var result =（DateTime）cmd.ExecuteScalar（）; 
 Console.WriteLine（Kind：+ result.Kind）; 
 Console.WriteLine（ToString（）：+ result.ToString（））; 
 Console.WriteLine（添加1分钟，大于UtcNow？
 +（result.AddMinutes（1）> DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（添加1分钟，大于现在
 +（result.AddMinutes（1）> DateTime.Now）.ToString（））; 
 Console.WriteLine（Add 1 minute，than UtcNow？
 +（result.AddMinutes（1）< DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（Add 1 minutes，less than Now？
 +（result.AddMinutes（1）< DateTime.Now）.ToString（））; 
 Console.WriteLine（Subtract 1分钟，大于UtcNow？
 +（result.AddMinutes（-1）> DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（减去1分钟，大于现在
 +（result.AddMinutes（-1）> DateTime.Now）.ToString（））; 
 Console.WriteLine（Subtract 1 minute，than than UtcNow？
 +（result.AddMinutes（-1）< DateTime.UtcNow）.ToString（））; 
 Console.WriteLine（Subtract 1 minute，less than Now？
 +（result.AddMinutes（-1）< DateTime.Now）.ToString（））; 
  

在3:58 PM（本地，-7h）执行：

 种类：未指定
 ToString（）：6/3/2010 3:59:26 PM 
添加1分钟，大于UtcNow ？ False 
加1分钟，大于现在？ True 
添加1分钟，少于UtcNow？ True 
添加1分钟，小于现在？ False 
减去1分钟，大于UtcNow？假
减1分，大于现在？ False 
减去1分钟，小于UtcNow？真
减1分，小于现在？ True 
  

Using SQL Server 2008. This is a really junior question and I could really use some elaborate information, but the information on Google seems to dance around the topic quite a bit and it would be nice if there was some detailed elaboration on how this works...

Let's say I have a datetime column and in ADO.NET I set it to DateTime.UtcNow.

1) Does SQL Server store DateTime.UtcNow accordingly, or does it offset it again based on the timezone of where the server is installed, and then return it offset-reversed when queried? I think I know that the answer is "of course it stores it without offsetting it again" but want to be certain.

So then I query for it and cast it from object to a DateTime after getting it from, say, an IDataReader column. As far as I know, System.DateTime has metadata that internally tracks whether it is a UTC DateTime or it is an offsetted DateTime, which may or may not cause .ToLocalTime() and .ToUniversalTime() to have different behavior depending on this state. So,

2) Does this casted System.DateTime object already know that it is a UTC DateTime instance, or does it assume that it has been offset?

---

Now let's say I don't use UtcNow, I use DateTime.Now, when performing an ADO.NET INSERT or UPDATE.

3) Does ADO.NET pass the offset to SQL Server and does SQL Server store DateTime.Now with the offset metadata?

So then I query for it and cast it from, say, an IDataReader column to a DateTime.

4) Does this casted System.DateTime object already know that it is an offset time, or does it assume that it is UTC?

解决方案

Performed some unit tests to answer my own question in all four parts.

1: Does SQL Server store DateTime.UtcNow accordingly, or does it offset it again based on the timezone of where the server is installed, and then return it offset-reversed when queried?

Executed this):

cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)";
cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow));
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl";
Console.WriteLine(cmd.ExecuteScalar());

The result of this at 1:30 PM local time (-7h, or 8:30 PM UTC) was:

Jun  3 2010 8:30PM

Then I tried this:

cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)";
cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow));
cmd.ExecuteNonQuery();
Console.WriteLine("change time zone to utc");
Console.ReadLine();
cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl";
Console.WriteLine(cmd.ExecuteScalar());
Console.WriteLine("change time zone back to local");

Executed at 9:25 PM UTC, it returned

Jun  3 2010 9:25PM

Compare this to DateTime.Now:

cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)";
cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now));
cmd.ExecuteNonQuery();
Console.WriteLine("change time zone to utc");
Console.ReadLine();
cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl";
Console.WriteLine(cmd.ExecuteScalar());
Console.WriteLine("change time zone back to local");

Executed at 3:55 PM (local; -7h), returned:

Jun  3 2010  3:55PM

2: So then I query for it and cast it from object to a DateTime after getting it from, say, an IDataReader column. Does this casted System.DateTime object already know that it is a UTC DateTime instance, or does it assume that it has been offset?

Neither.

cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)";
cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow));
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT val value FROM testtbl";
var retval = (DateTime)cmd.ExecuteScalar();
Console.WriteLine("Kind: " + retval.Kind);
Console.WriteLine("UTC: " + retval.ToUniversalTime().ToString());
Console.WriteLine("Local: " + retval.ToLocalTime().ToString());

The result of this (executed at 1:58 PM local time) was:

Kind: Unspecified
UTC: 6/4/2010 3:58:42 AM
Local: 6/3/2010 1:58:42 PM

That is, .ToUniversalTime() ended up offsetting from local time to UTC time not once but twice (??), and .ToLocalTime() ended up not offsetting at all.

3: Does ADO.NET pass the offset to SQL Server and does SQL Server store DateTime.Now with the offset metadata?

Without performing any unit tests, the answer is already known to be "only with DateTimeOffset" SQL type. SQL's datetime does not do offsets.

4: Does this casted System.DateTime object already know that it is an offset time, or does it assume that it is UTC?

Neither. SQL's DateTimeOffset type is returned as a .NET DateTimeOffset struct.

The following executed at 3:31 PM local time where column offval is a datetimeoffset SQL type,

cmd.CommandText = "INSERT INTO testtbl (offval) VALUES (@newval)";
cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now));
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT offval value FROM testtbl";
object retvalobj = cmd.ExecuteScalar();
Console.WriteLine("Type: " + retvalobj.GetType().Name);
var retval = (DateTimeOffset)retvalobj;
Console.WriteLine("ToString(): " + retval.ToString());
Console.WriteLine("UTC: " + retval.ToUniversalTime().ToString());
Console.WriteLine("Local: " + retval.ToLocalTime().ToString());

This resulted in:

Type: DateTimeOffset
ToString(): 6/3/2010 3:31:47 PM +00:00
UTC: 6/3/2010 3:31:47 PM +00:00
Local: 6/3/2010 8:31:47 AM -07:00

A surprising disparity.

---

Going back and executing the test for question #1 above using DateTime.Now instead of DateTime.UtcNow, I validated that ADO.NET does NOT convert to universal time before storing to the database.

That is, this executed at 3:27 PM local time (-7h):

 cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)";
 cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now));
 cmd.ExecuteNonQuery();
 Console.WriteLine("change time zone to utc");
 Console.ReadLine();
 cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl";
 Console.WriteLine(cmd.ExecuteScalar());
 Console.WriteLine("change time zone back to local");

.. returned ..

Jun  3 2010  3:27PM

Executing this at 3:17 PM local time:

cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)";
cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow));
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT val FROM testtbl";
var result = (DateTime)cmd.ExecuteScalar();
Console.WriteLine("Kind: " + result.Kind);
Console.WriteLine("ToString(): " + result.ToString());
Console.WriteLine("Add 1 minute, is greater than UtcNow? "
 + (result.AddMinutes(1) > DateTime.UtcNow).ToString());
Console.WriteLine("Add 1 minute, is greater than Now? "
 + (result.AddMinutes(1) > DateTime.Now).ToString());
Console.WriteLine("Add 1 minute, is less than UtcNow? "
 + (result.AddMinutes(1) < DateTime.UtcNow).ToString());
Console.WriteLine("Add 1 minute, is less than Now? "
 + (result.AddMinutes(1) < DateTime.Now).ToString());
Console.WriteLine("Subtract 1 minute, is greater than UtcNow? "
 + (result.AddMinutes(-1) > DateTime.UtcNow).ToString());
Console.WriteLine("Subtract 1 minute, is greater than Now? "
 + (result.AddMinutes(-1) > DateTime.Now).ToString());
Console.WriteLine("Subtract 1 minute, is less than UtcNow? "
 + (result.AddMinutes(-1) < DateTime.UtcNow).ToString());
Console.WriteLine("Subtract 1 minute, is less than Now? "
 + (result.AddMinutes(-1) < DateTime.Now).ToString());

Resulted in:

Kind: Unspecified
ToString(): 6/3/2010 10:17:05 PM
Add 1 minute, is greater than UtcNow? True
Add 1 minute, is greater than Now? True
Add 1 minute, is less than UtcNow? False
Add 1 minute, is less than Now? False
Subtract 1 minute, is greater than UtcNow? False
Subtract 1 minute, is greater than Now? True
Subtract 1 minute, is less than UtcNow? True
Subtract 1 minute, is less than Now? False

Compare this to DateTime.Now:

cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)";
cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now));
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT val FROM testtbl";
var result = (DateTime)cmd.ExecuteScalar();
Console.WriteLine("Kind: " + result.Kind);
Console.WriteLine("ToString(): " + result.ToString());
Console.WriteLine("Add 1 minute, is greater than UtcNow? "
 + (result.AddMinutes(1) > DateTime.UtcNow).ToString());
Console.WriteLine("Add 1 minute, is greater than Now? "
 + (result.AddMinutes(1) > DateTime.Now).ToString());
Console.WriteLine("Add 1 minute, is less than UtcNow? "
 + (result.AddMinutes(1) < DateTime.UtcNow).ToString());
Console.WriteLine("Add 1 minute, is less than Now? "
 + (result.AddMinutes(1) < DateTime.Now).ToString());
Console.WriteLine("Subtract 1 minute, is greater than UtcNow? "
 + (result.AddMinutes(-1) > DateTime.UtcNow).ToString());
Console.WriteLine("Subtract 1 minute, is greater than Now? "
 + (result.AddMinutes(-1) > DateTime.Now).ToString());
Console.WriteLine("Subtract 1 minute, is less than UtcNow? "
 + (result.AddMinutes(-1) < DateTime.UtcNow).ToString());
Console.WriteLine("Subtract 1 minute, is less than Now? "
 + (result.AddMinutes(-1) < DateTime.Now).ToString());

Executed at 3:58 PM (local, -7h):

Kind: Unspecified
ToString(): 6/3/2010 3:59:26 PM
Add 1 minute, is greater than UtcNow? False
Add 1 minute, is greater than Now? True
Add 1 minute, is less than UtcNow? True
Add 1 minute, is less than Now? False
Subtract 1 minute, is greater than UtcNow? False
Subtract 1 minute, is greater than Now? False
Subtract 1 minute, is less than UtcNow? True
Subtract 1 minute, is less than Now? True

这篇关于ADO.NET + SQL Server DateTime列的生命周期中如何处理时区？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！