Timestamp issue from ojdbc5 & ojdbc

Question

JVM Timezone details : India Standard TimeAsia/Calcutta019800000

DB Timezone details : Central Standard TimeAmerica/Chicago3600000-21600000

Sample Code:

String sql = "select systimestamp as base from dual";
....
PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
    System.out.println(rs.getTimestamp("base"));
    System.out.println(rs.getString("base"));
}

Test-1: I tested using ojdbc14.jar 10.1.0.3.0 with, it works as expected printing the db time

Output:

2013-12-05 01:23:57.141583 
2013-12-5 1.23.57.141583000 -6:0 

Test-2: I tested using ojdbc5.jar & ojdbc6.jar 11.2.0.3.0 with, getTimestamp print local time where as getString prints db time.

Output:

2013-12-05 12:57:54.3508 
2013-12-05 01:27:54.3508 -6:00 

Please suggest what is wrong with Test-2 ojdbc driver version 11.2.0.3.0 where the getTimestamp() prints local time. My application expect the db time like Test-1 in the getTimestamp() where it matches with DB timestamp.

Answer 1

We had the same issue a few days ago at my company. The bottom line of this is you should never get a timestamp as a string (as it's database/driver specific) and always get the TimeStamp object and format it any way you want using a SimpleDateFormat.

And it's always worth mentioning that the Timestamp class is an abomination , from the javadocs

Due to the differences between the Timestamp class and the java.util.Date class mentioned above, it is recommended that code not view Timestamp values generically as an instance of java.util.Date. The inheritance relationship between Timestamp and java.util.Date really denotes implementation inheritance, and not type inheritance.