sql-server - 在 ADO.NET + SQL Server DateTime 列的生命周期中如何处理时区？

Question

使用 SQL Server 2008。这是一个非常初级的问题，我确实可以使用一些详细的信息，但是 Google 上的信息似乎围绕这个主题进行了很多讨论，如果有一些关于如何使用的详细说明，那就太好了这有效...

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

1) SQL Server 是否相应地存储 DateTime.UtcNow，还是根据服务器安装位置的时区再次偏移它，然后在查询时返回偏移量反转的值？我想我知道答案是“当然它会存储它而不会再次抵消它”，但想确定一下。

然后我查询它，并在从 IDataReader 列获取它后将其从对象转换为 DateTime。据我所知， System.DateTime 具有内部跟踪它是 UTC DateTime 还是偏移 DateTime 的元数据，这可能会或可能不会导致 .ToLocalTime() 和 .ToUniversalTime() 根据此状态具有不同的行为。所以，

2) 这个转换的 System.DateTime 对象是否已经知道它是 UTC DateTime 实例，还是假设它已被偏移？

<小时/>

现在假设我在执行 ADO.NET INSERT 或 UPDATE 时不使用 UtcNow，而是使用 DateTime.Now。

3) ADO.NET 是否将偏移量传递给 SQL Server，并且 SQL Server 是否使用偏移量元数据存储 DateTime.Now？

然后我查询它并将其从 IDataReader 列转换为 DateTime。

4) 这个转换的 System.DateTime 对象是否已经知道它是偏移时间，还是假设它是 UTC？

Answer 1

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

###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(-7 点，或 UTC 晚上 8:30)的结果是:

Jun  3 2010 8:30PM

然后我尝试了这个:

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

于 UTC 时间晚上 9:25 执行，返回

Jun  3 2010 9:25PM

将其与 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");

下午 3:55 执行(本地；-7h)，返回:

Jun  3 2010  3:55PM

###2:然后我查询它，并在从 IDataReader 列获取它后将其从对象转换为 DateTime。这个转换的 System.DateTime 对象是否已经知道它是 UTC DateTime 实例，还是假设它已被偏移？

都不是。

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 执行)是:

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

也就是说，.ToUniversalTime() 最终从本地时间偏移到 UTC 时间不是一次而是两次 (??)，而 .ToLocalTime() 最终没有完全抵消。

###3:ADO.NET 是否将偏移量传递给 SQL Server，SQL Server 是否使用偏移量元数据存储 DateTime.Now？

在不执行任何单元测试的情况下，已知答案是“仅使用 DateTimeOffset”SQL 类型。 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());

这导致:

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

令人惊讶的差异。

<小时/>

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

也就是说，它在本地时间下午 3:27(-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");

..返回..

Jun  3 2010  3:27PM

在本地时间下午 3:17 执行此操作:

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

结果:

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

将其与 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());

下午 3:58 执行(本地时间，-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