如何从SQL Server获取DateTime数据而忽略时区问题? [英] How can I get DateTime data from SQL Server ignoring time zone issues?

问题描述

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

My situation is that we store data in a SQL Server database, supporting 2005 on up. When a DateTime value is stored, it is in the local time of the client. I need to be able to get that date back on any other client, anywhere, without respect to whatever time zone that other client might be in.

例如，当纽约的用户输入DateTime值"2012-12-20 00:00"时，我希望加利福尼亚的用户看到相同的DateTime值.这些DateTime值不应尊重时区的差异，但这就是我所看到的.当前，SQL Server会将该DateTime作为"2012-12-19 21:00"交付给加利福尼亚州的用户.请注意，由于从EST到PST的更改而导致了-3小时的回滚，因此现在是前一天(请忽略本次对话中的DST问题).

So for instance, when a user in New York enters a DateTime value of "2012-12-20 00:00", I want the user in California to see that very same DateTime value. These DateTime values should not respect the difference in time zones, but that is what I see happening. Currently, SQL Server would deliver that DateTime to the California user as "2012-12-19 21:00". Notice how it's now the previous day because of the -3 hour rollback due to the change from EST to PST (forget about DST issues for the purposes of this conversation).

我需要完成将数据逐字取回，而不是按时区进行翻译的操作.那么您能提供什么建议?

Getting that data back verbatim, and not translated by time zone, is what I need to accomplish. So what advice can you offer?

要考虑的一些代码:

这是桌子:

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对象:

We have a Sql class that does the work, and in the following example "sql" is an object of that class. The call is made using a parameterized query where @data0 is the DateTime object to store in a SQL DateTime field:

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

最终，这将设置SqlCommand，分配参数并触发命令.ExecuteNonQuery()调用.

Ultimately, this sets up an SqlCommand, assigns parameters, and fires a command.ExecuteNonQuery() call.

现在，要检索日期，我只需将其选择到DataTable中(再次使用Sql类帮助器):

Now, to retrieve the date, I simply select it into a DataTable (again, using our Sql class helper):

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

所以我看到的是数据库本身中的日期如我所料，为"2012-12-20 00:00"，但是当我在调试中检查myTable内容时，我看到该表中的日期为"2012-12-19 21:00".

So what I see is that in the database itself, the date is "2012-12-20 00:00" as I expect, but when I inspect the myTable contents in debug, I see that the date in that table is "2012-12-19 21:00".

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

The database was created on a SQL Server that runs on a machine in an EST state. My machine, however, is set to PST. Hence the difference in how the DateTime value is delivered to me in the SELECT.

我想念什么?

推荐答案

感谢大家的投入，并根据您的建议进行了一些研究，我们已经解决了问题.

Thanks to the input from you fine folks, and some research along the lines of your suggestions, we have resolved our issue.

特别感谢 ebyrob 的帮助以及此链接:

A special thanks to ebyrob for the assistance and this link: http://support.microsoft.com/kb/842545

该链接最终被证明是灵丹妙药.

That link ultimately proved to be the silver bullet.

基本问题是，在构造DataTable或DataSet时，将使用它编码有关创建它的时区的信息.然后，如果将该对象通过连接传递到位于不同时区中的计算机，则其中的任何DateTime值都会根据时区中的差异进行调整.

The fundamental issue is that when you construct a DataTable or DataSet, information about the time zone in which it is created is encoded with it. Then, if you pass that object across a connection to a machine that exists within a different time zone, any DateTime values in there will be adjusted based on the difference in time zones.

基于这种理解，解决方法是将所有DateTime数据列的 DateTimeMode 属性设置为 DataSetDateTime.Unspecified .这样可以防止序列化后的日期调整，而是逐字传递DateTime值.

In light of that understanding, the fix is to set the DateTimeMode property of all DateTime DataColumns to DataSetDateTime.Unspecified. This prevents the post-serialization date adjustments, instead delivering the DateTime values verbatim.

我还想指出，该文章指定了DataSet，但我们已经证明DataTable也很容易受到攻击.实际上，我非常确定它归结于DataColumn本身(无论如何，它们都是DateTime类型的).我认为这篇文章也指出了这一点.

I also want to point out that the article specifies DataSet, but we have proven DataTable is also vulnerable. In fact, I'm quite certain that it comes down to the DataColumn itself (those with a type of DateTime, anyway). I think the article states this as well.

再次感谢；我想我们已经知道了！

So thanks again; I think we've got it now!

这篇关于如何从SQL Server获取DateTime数据而忽略时区问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！