Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Confusing timezone behavior in ResultSet.getTimestamp()

David BalažicJul 3 2023 — edited Jul 3 2023

I noticed that some of my testcases fail due to a one hour difference in a timestamp value.

Details:

Database table column type: TIMESTAMP WITH LOCAL TIME ZONE
Java object field type: java.util.Date (the value is ultimately assigned to such a variable, but the problem happens before)

Basically, Java calls a stored procedure that has an OUT parameter of type sys_refcursor and reads the timestamp column from the resultset with java.sql.ResultSet.getTimestamp(int columnIndex)

This call ends up as oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(int columnIndex)

Accessing the DB with SQLPLus gives:

> select DBTIMEZONE, SESSIONTIMEZONE from dual;

DBTIME SESSIONTIMEZONE
------ ---------------------------
+00:00 +02:00

The data in the table was inserted as:

> insert into TABLE1 ( TS_COLUMN, …) values (TIMESTAMP '2015-01-01 11:01:01', …);

> select TS_COLUMN from TABLE1;

TS_COLUMN
----------------------------------------
01-JAN-15 11.01.01.000000 AM

> var c refcursor
> exec the_procedure('some parameters',:c);

PL/SQL procedure successfully completed.

SQL> print

TS_COLUMN …. other columns omitted
----------------------------------------
01-JAN-15 11.01.01.000000 AM

Java code (on the same OS, same user) in the other hand reads it as (as returned by OracleResultSetImpl.getTimestamp):

java.sql.Timestamp with value 2015-01-01 10:01:01.0 (one hour less!)

It seems like a timezone issue and discovered, that normally, in the testcase code:

System.out.println("user.timezone = "+System.getProperty("user.timezone"));
System.out.println("TZ.def = " + TimeZone.getDefault());

prints:

user.timezone = Europe/Prague
TZ.def = sun.util.calendar.ZoneInfo[id="Europe/Prague",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Prague,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]]

But if I add the JVM parameter -Duser.timezone=BLABLA , then the output changes to:

user.timezone = GMT+02:00
TZ.def = sun.util.calendar.ZoneInfo[id="GMT+02:00",offset=7200000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]

And OracleResultSetImpl.getTimestamp() now returns 2015-01-01 11:01:01.0 which is correct.

So if I set a non-existing timezone, the code behaves correctly. Why?

My OS: Windows 10
timezone in OS: Central European Time
Environment where the Java testcase is started:

Eclipse IDE for Enterprise Java and Web Developers (includes Incubating components)
Version: 2023-03 (4.27.0)
Build id: 20230309-1520

JRE: Oracle JDK 11.0.19

ojdbc: ojdbc6_g-11.2.0.2.0.jar (yes, ancient, it is a legacy project)

Database: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Comments
Post Details
Added on Jul 3 2023
1 comment
552 views