Oracle的日期和时间不通过JDBC节省夏令时 [英] Oracle's date and time without daylight savings via JDBC

问题描述

通过PL/SQL，ODBC和JDBC发出以下SQL会产生不同的结果:

Issuing the following SQL generates different results thru PL/SQL, ODBC and JDBC:

select sysdate from dual

在PL/SQL或ODBC上运行时，日期和时间正确.在JDBC上，它减少了一个小时.似乎没有考虑夏令时.

When running it on PL/SQL or ODBC, the date and time is correct. On JDBC it comes with an hour less. It seems that it is not considering daylight savings.

例如，在PL/SQL上，结果为2012-11-05 16:53:53.0，在JDBC上，结果为2012-11-05 15:53:53.0.

For example, on PL/SQL the result is 2012-11-05 16:53:53.0 and on JDBC it is 2012-11-05 15:53:53.0.

它仅在某些数据库上发生.更改数据库时区(select dbtimezone from dual)似乎不会影响结果.

It happens only on some databases. Changing the database timezone (select dbtimezone from dual) doesn't seems to affect the results.

该命令正在巴西执行.由于夏令时，原始GMT偏移量为-03:00，当前偏移量为-02:00.

The command is executing on Brazil. Raw GMT offset is -03:00, current offset is -02:00 because of daylight savings.

客户端JVM的时区数据库是最新的.

The timezone database of the client JVM is up-to-date.

要诊断数据库中的错误"结果，只需打印结果:

To diagnose the "wrong" result from database, just print the result:

((OracleResultSet) statement.executeQuery("select sysdate from dual")).getTIMESTAMP(1).toString();

Oracle的TIMESTAMP toString方法不依赖时区信息. JVM的时区可能只影响创建TIMESTAMP之前的结果，即从网络读取并将其转换为Java表示形式时的结果.

Oracle's TIMESTAMP toString method do not rely on timezone information. The JVM's timezone may only affect the result before the creation of the TIMESTAMP, i.e. while reading from the network and transforming it into a representation in Java.

测试同时更改客户端和数据库服务器的时间配置:

Tests on changing both client and database server time configuration:

• SYSDATE始终返回数据库服务器中解析的日期/时间，客户端JVM的user.timezone选项和客户端的机器时间配置无关紧要.
另一方面，使用两个时区信息都可以解决获取SYSTIMESTAMP的问题:看起来它从UTC中的服务器获取日期和时间，然后在客户端中应用时区以获取本地日期和时间.

• SYSDATE always return the date/time resolved in the database server, the client JVM's user.timezone option and client's machine time configuration do not matter.
• On the other hand, getting SYSTIMESTAMP is resolved using both timezone informations: looks like it gets the date and time from server in UTC and then apply the timezone in the client to get a local date and time.

客户端运行Windows，服务器运行Linux.

Client is running Windows, server is running Linux.

要使事情变得更奇怪，发出TO_CHAR也会产生错误的结果:

To get things more weird, issuing a TO_CHAR yield the wrong result too:

select TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') from dual

• 直接在Oracle上:06/11/2012, 10:38:49
• 在Java上:06/11/2012 09:38:49
• Directly on Oracle: 06/11/2012, 10:38:49
• On Java: 06/11/2012 09:38:49

Oracle服务器:

Oracle servers:

[root@oracle1 ~]# cat /etc/sysconfig/clock
ZONE="America/Sao_Paulo"
UTC=false
ARC=false
[root@oracle1 ~]# echo $TZ

[root@oracle1 ~]# date
Tue Nov 13 14:58:38 BRST 2012
[root@oracle1 ~]#


[root@oracle2 ~]# cat /etc/sysconfig/clock
ZONE="America/Sao_Paulo"
UTC=false
ARC=false
[root@oracle2 ~]# echo $TZ

[root@oracle2 ~]#  date
Tue Nov 13 14:59:58 BRST 2012
[root@oracle2 ~]#

有什么想法吗?我应该从数据库收集什么信息或配置以诊断和解决此问题?

Any thoughts? What info or configuration should I collect from the database to diagnose and solve this problem?

推荐答案

简单地说，在Java Date中选择一个Oracle DATE本质上是有问题的.那是因为它们根本不同. Oracle DATE是年，月，日，小时，分钟，秒的组合，没有任何时区信息，因此它可以是任何时区，带有或不带有夏令时-Oracle不知道，因为该信息是"t包含在DATE中.

Simply put, selecting an oracle DATE into a Java Date is inherently problematic. That's because they are fundamentally different. An Oracle DATE is the combination of year, month, day, hours, minute, seconds, without any timezone information, so it could be any timezone, with or without daylight saving - Oracle doesn't know, since that information isn't included in the DATE.

另一方面，Java日期基本上是自1970年1月1日00:00:00 UTC以来的毫秒数.

On the other hand, a Java Date basically is the number of milliseconds since 1/1/1970 00:00:00 UTC.

当Oracle DATE进入Java Date时，JDBC驱动程序只能猜测要应用哪个时区.结果相当难以预测，尤其是当数据库中的数据使用的不是用户时区时.

When an Oracle DATE goes into a Java Date, the JDBC driver can only guess which timezone to apply. The results are rather unpredictable, especially when the data in the database use another timezone than the user.

这篇关于Oracle的日期和时间不通过JDBC节省夏令时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！