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