如何从SQL Server获取DateTime数据并忽略时区问题？

Question

我的情况是，我们将数据存储在SQL Server数据库中，支持2005年及以上版本。当存储DateTime值时，它是客户端的本地时间。我需要能够在任何其他客户端上获取该日期，而不考虑其他客户端可能处于哪个时区。

例如，当纽约用户输入“2012-12-20 00:00”的DateTime值时，我希望加利福尼亚用户看到完全相同的DateTime值。这些DateTime值不应考虑时区差异，但这就是我看到的情况。目前，SQL Server会将该DateTime作为“2012-12-19 21:00”交付给加利福尼亚用户。请注意，由于从EST到PST的更改而导致的-3小时回滚，现在已经是前一天了（为了本次对话的目的，请忘记DST问题）。

实现原样获取数据，而不是按时区转换，这就是我需要完成的任务。那么您能提供什么建议呢？

以下是需要考虑的一些代码：

这是表格：

CREATE TABLE [dbo].[tblMeterReadings](
[fldMeterReadingsID] [int] IDENTITY(1,1) NOT NULL,
[fldMeterID] [int] NOT NULL,
[fldUser] [varchar](50) NULL,
[fldBy] [varchar](50) NULL,
[fldDateOfReading] [datetime] NULL,
[fldReading] [float] NULL,
[fldIsArchived] [bit] NULL DEFAULT ((0)),

我们有一个名为Sql的类来执行操作，在以下示例中，“sql”是该类的一个对象。调用时使用参数化查询，其中@data0是要存储在SQL DateTime字段中的DateTime对象：

sql.Execute(@"INSERT INTO tblMeterReadings (fldMeterID, fldDateOfReading) VALUES (" + MeterID + ", @data0)", Date);

最终，这将设置一个SqlCommand，分配参数并触发command.ExecuteNonQuery()调用。

现在，要检索日期，我只需将其选择到DataTable中（同样使用我们的Sql类助手）：

DataTable myTable = sql.readDataTable(@"SELECT fldMeterID, fldDateOfReading FROM tblMeterReadings");

我看到的是在数据库本身中，日期是"2012-12-20 00:00"，正如我所期望的那样，但是当我检查调试中的myTable内容时，我发现该表中的日期是"2012-12-19 21:00"。

数据库是在运行于EST状态的计算机上的SQL Server上创建的。然而，我的计算机设置为PST。因此，在SELECT中DateTime值传递给我的方式有所不同。

我错过了什么？

- DonBoitnott

---

相关链接：https://dev59.com/U3E85IYBdhLWcg3w8IbK - Oded

你是如何将日期写入数据库的？你的问题可以通过在保存到数据库之前从日期时间中删除时区信息来解决。 - Daniel Hilgarth

@DanielHilgarth - 如果您的意思是将其存储为UTC时间，那么我同意。但是已经有很多数据存在，除非我们能够通过大规模的转换，否则我只能尝试按照现有的方式处理它。 - DonBoitnott

除了像其他人建议的那样发布您的代码，您还应该提供存储DateTime值的表的定义。 - phoog

@DonBoitnott：我不是指将其存储为UTC。这仍然会在纽约和加利福尼亚州显示不同。我是指存储没有时区信息，因为这是您想要的。 - Daniel Hilgarth

包括 tblReadings 表的定义。 - Remus Rusanu

Answer 1

在数据库中存储UTC日期。仅在客户端显示时将其转换为本地时间。

在服务器端，使用getutcdate()而不是getdate()。

- Andomar

---

我完全同意。不幸的是，短期内这是不可能的。事实上，有些客户已经拥有20多年的数据了。这实际上源于我们总是在Access数据库中“逃脱”的事实，而最近转移到SQL Server则暴露了这个弱点。 - DonBoitnott

Answer 2

感谢您的建议和相关研究，我们已经解决了问题。

特别感谢ebyrob提供的帮助和这个链接：http://support.microsoft.com/kb/842545

这个链接最终证明是解决问题的关键。

根本问题在于构建DataTable或DataSet时，其中包含了它所处的时区信息。然后，如果你将该对象传递到存在不同时区的机器上，其中的任何DateTime值都将根据时区差异进行调整。

基于这种理解，解决方法是将所有DateTime DataColumns的DateTimeMode属性设置为DataSetDateTime.Unspecified。这可以防止序列化后的日期调整，而是直接提供DateTime值。

我还想指出，虽然文章中指定了DataSet，但我们已经证明DataTable也是易受攻击的。实际上，我相当确定它归结于DataColumn本身（特别是那些类型为DateTime的）。我认为文章也指出了这一点。

再次感谢; 我们现在已经掌握了它！

- DonBoitnott

---

感谢您发布调查结果。 - Remus Rusanu

Answer 3

无论您想在哪里查看特定时区的日期，您都可以这样做：

DateTime dtDateOfReading = TimeZoneInfo.ConvertTime(
    Convert.ToDateTime(myTables.Rows[i]["fldDateOfReading"]), 
    dtTimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time")
    );

如果您想查看多个时区的内容，您需要将时区与日期或客户站点一起存储。

注意：要小心转换后的DateTime值，因为它们的Kind为Unspecified，在某些情况下可能会被错误地认为是本地时间。

编辑：将SQL连接的时区设置为与SQL Server的时区匹配可能更容易，但是这篇文章http://support.microsoft.com/kb/842545似乎表明SqlDataAdapter可能不支持时区配置。

- user645280

---

我考虑过这个问题。但是这种方法不是假设我知道DateTime值创建的时区吗？我的例子可能会暗示这一点，但事实并非如此。我只是有一个DateTime，但必须假设我对它一无所知。事实上，我甚至不关心。如果它在数据库中是“2012-12-20 00:00”，那就是我想要的，就好像根本不存在时区一样。 - DonBoitnott

你知道SQL服务器的时区是EST。你可能也知道，例如所有客户NYSEG的时间都是以EST为准。（我认为你真正关注的是服务器时区的差异，而不是个别记录的时区...） - user645280

我稍微找了一下关于如何“设置SQL连接的时区”的方法，就像你在编辑中提到的那样，但是我完全不知道该怎么做。你能给我指点一个页面或者提供一行示例代码吗？顺便说一句，那篇知识库文章写得很好，我觉得正是我面临的根本问题。 - DonBoitnott

不幸的是，这就是KB文章的重点...我认为你无法改变它的行为，因为这是“按设计来的”。 - user645280