Oracle.ManagedDataAccess错误地读取了DST日期 [英] Oracle.ManagedDataAccess reads DST dates incorrectly

问题描述

我正在编写一个运行在Oracle 11.2.0.2.0数据库之上的.Net应用程序，该数据库将日期存储在"TIMESTAMP(6)WITH LOCAL TIME ZONE"类型的列中.如果有一个日期存储在该列中并且该日期在DST内，则使用Oracle.ManagedDataAccess库时该日期将被错误地读取.它似乎总是正确地写入/更新日期.另外，在使用Oracle.DataAccess库时，它始终可以正确处理日期.

I am writing a .Net application that runs on top of an Oracle 11.2.0.2.0 database that stores dates in columns of type "TIMESTAMP(6) WITH LOCAL TIME ZONE". When there is a date stored into the column and it falls within DST, the date is read incorrectly when using the Oracle.ManagedDataAccess library. It appears to always write/update dates correctly. Also, when using the Oracle.DataAccess library, it always handles the dates correctly.

在我的示例中，我使用时区"America/New_York"，日期/时间为2014年8月1日12:00:00.这是一段错误地读取日期的代码:

In my example I am using timezone 'America/New_York' and date/time of 08/01/2014 12:00:00. Here is a snippet of code that reads the date incorrectly:

IDbConnection cxn = new Oracle.ManagedDataAccess.Client.OracleConnection(ConnStr);
// Using the following library works correctly: 
// IDbConnection cxn = new Oracle.DataAccess.Client.OracleConnection(ConnStr);
cxn.Open();

var cmd = cxn.CreateCommand();
cmd.CommandText = "alter session set time_zone='America/New_York'";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT TEST_DATE FROM TEST_TABLE WHERE ROWNUM=1";
return (DateTime)cmd.ExecuteScalar();

当该列的值更新为08/01/2014 12:00:00时，将其读取为08/01/2014 11:00:00.如果我使用的日期不在DST(例如12/01/2014 12:00:00)之内，它将正确读取该日期.有什么想法吗?我一直在搜索，但未找到有关此问题的任何文档.我可能不得不切换回Oracle.DataAccess，但是希望避免这种情况.预先感谢！

When the column is updated with value 08/01/2014 12:00:00, it is read as 08/01/2014 11:00:00. If I use a date that does not fall within DST (such as 12/01/2014 12:00:00), it reads the date correctly. Any ideas on this? I have been searching all over but have not found any documentation on this issue. I may have to switch back to the Oracle.DataAccess, but was hoping to avoid it. Thanks in advance!

推荐答案

Oracle.ManagedDataAccess仍然很新，因此您也总是得到最新"的错误.

Oracle.ManagedDataAccess is still quite new, so you also get always the "newest" bugs.

还有其他方法可以定义您当前的会话时区，也许是以下作品之一.

There are others ways to define your current session timezone, maybe one of the following works.

• OracleGlobalization 类的用法:

this.Connection = new OracleConnection();
this.Connection.ConnectionString = ...
this.Connection.Open();
OracleGlobalization info = this.Connection.GetSessionInfo();
info.TimeZone = "America/New_York";
this.Connection.SetSessionInfo(info);

请非常仔细地进行测试，我在OracleGlobalization方面的经验很差.还测试this.Connection.OpenWithNewPassword(...);，不仅要测试this.Connection.Open();.当我使用OpenWithNewPassword时，我的应用程序崩溃了而没有任何错误(即使在Visual Studio中调试时也是如此！)

Test this very carefully, my experience with OracleGlobalization is quite bad. Test also this.Connection.OpenWithNewPassword(...);, not only this.Connection.Open();. When I used OpenWithNewPassword my application was crashing without any error (even while debugging in Visual Studio!)

在系统中将ORA_SDTZ设置为环境变量.

Set ORA_SDTZ as Environment variable in your system.

在注册表中设置时区，它是HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_{YOUR_ORACLE_HOME_NAME}\ORA_SDTZ的字符串值，例如

Set timezone in your Registry, it is a String Value for HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_{YOUR_ORACLE_HOME_NAME}\ORA_SDTZ, e.g.

对于x64(64位)应用程序

For a x64 (64 bit) Application

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1]
"ORA_SDTZ"="America/New_York"

对于x86(32位)应用程序

For a x86 (32 bit) Application

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1]
"ORA_SDTZ"="America/New_York"

注意，ODP.NET托管驱动程序不会读取任何注册表值，因此更多地用作其他驱动程序的信息！

Note, ODP.NET Managed Driver does not read any Registry values, so this is more as information for other drivers!

这篇关于Oracle.ManagedDataAccess错误地读取了DST日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！