Hello,
when changing the session time_zone, the JDBC driver returns wrong values for CURRENT_TIMESTAMP.
The following (simplified code) demonstrates this. The SQL statement retrieves the current_timestamp and just the time from current_timestamp extracted using to_char() to avoid any conversion by the JDBC driver.
OracleConnection con = (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oradb", "foo", "bar");
stmt = con.createStatement();
String sql =
"select current_timestamp, \n" +
" to_char(current_timestamp, 'hh24:mi') as current_time \n" +
"from dual";
ResultSet rs = stmt.executeQuery(sql);
rs.next();
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
Timestamp ts = rs.getTimestamp(1);
System.out.println("current_timestamp: " + sdf.format(ts));
System.out.println(" to_char: " + rs.getString(2));
rs.close();
// it doesn't matter what is used to change the time_zone:
conn.setSessionTimeZone("-05:00");
// stmt.execute("alter session set time_zone = '-05:00'");
rs = stmt.executeQuery(sql);
rs.next();
ts = rs.getTimestamp(1);
System.out.println("current_timestamp: " + sdf.format(ts));
System.out.println(" to_char: " + rs.getString(2));
rs.close();
This will output the following:
current_timestamp: 20:21
to_char: 20:21
current_timestamp: 20:21
to_char: 13:21
As you can see the time returned in the Timestamp instance is the wrong when the session time zone is changed (the value returned by call to_char() does reflect the correct value).
Is there any connection property that can be set in order to avoid this error?
Environment:
Java 6 (but this also happens with Java 7 and Java 8)
JDBC Driver version 11.2.0.3 (ojdbc6.jar) and 11.2.0.4
Oracle 11.2.0.3 and Oracle 11.2.0.4 running on Windows 7 or Windows Server 2008
Thanks
Thomas