Oracle's date and time without daylight savings via JDBC

Question

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

select sysdate from dual

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.

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.

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

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

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'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 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.

Client is running Windows, server is running Linux.

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

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

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?

Answer 1

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.

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

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.

Answer 2

Java Dates don't have timezones until you format them.

A java date is internally stored as a long - the number of milliseconds elapsed since Jan 1, 1970, midnight UTC.

Look at the java Date's time, along with it's reported time zone (however you format it), and you'll probably see that it is equivalent to the time in the database.

You most likely have a problem with formatting the date - using a timezone other than the one that you expect. I'm guessing that you are not specifying the time zone (using the default one) when formatting, or simply using Date's toString() method.

If you use the Calendar (calendar.setTime(date)), you can combine the 'epoch time' with a specific time zone, or if it works for you, use the default time zone. You can also query a Calendar for it's timezone, and if the default one is incorrect, then you will indeed need to investigate whether either the computer itself is set to the wrong timezone, or whether something is amiss with the java timezone database on that computer.

Answer 3

"Java Dates don't have timezones until you format them." Wrong, it is defined as UTC. The original answer by ammoQ is correct. Database Date columns don't have timezone, although it may be stored as milliseconds since midnight 1 Jan 1970, it isn't defined as UTC, it is any timezone you like (ie same local time regardless of timezone).

In particular, JDBC uses the JVM's default timezone when converting to java Date in UTC. So if the database stores 1/1/2014 02:00, and your JVM is "America/Chicago" then you get a java Date equivalent to 1/1/2014 02:00 CST. If your JVM is "America/New_York" then you get a java Date equivalent to 1/1/2014 02:00 EST. This is a very different moment in time, an hour (3600000 milliseconds) differnt, which happens to be equivalent to 1/1/2014 01:00 CST, not 02:00 CST.

However the biggest issue with using the JVM's timezone occurs every year during the "overlap hour" if that timezone honors Daylight Saving Time. The database will say 01:30 on the fall-back Sunday (in US timezones), but which 01:30 is it, the first in Daylight Time or the second after the switch back to Standard Time? The JDBC/JVM has to arbitrarily choose between these two moments in time. As I recall, I think most JVMs will always use Standard Time (the second 01:30). Although during the Spring jump to Daylight Saving Time, a time of 02:30 technically does not exist, but I think most JVMs will assume 03:30 after the spring-forward to Daylight time.

Answer 4

Try vm parameter

-Duser.timezone=GMT-2

or

-Duser.timezone=America/Sao_Paulo

Java has its own timezone settings and it may affect transfer of Date objects.