PreparedStatement and setTimestamp in oracle jdbc

Question

Hi everyone,

I am using PreparedStatement with Timestamp in where clause:

PreparedStatement s=c.prepareStatement("select value,utctimestamp from t where utctimestamp>=? and utctimestamp<?"); 
s.setTimestamp(1, new Timestamp(1273017600000L));   //2010-05-05 00:00 GMT
s.setTimestamp(2, new Timestamp(1273104000000L));   //2010-05-06 00:00 GMT
ResultSet rs = s.executeQuery();
if(rs.next()) System.out.println(rs.getInt("value"));

The result I get is different, when I have different time zones on the client computer. Is this a bug in Oracle jdbc? or correct behavior?

Oracle database version is 10.2 and I have tried with oracle jdbc thin driver version 10.2 and 11.1.

The parameter is Timestamp, and I expected that no time conversions will be done on the way. The database column type is DATE, but I also checked it with TIMESTAMP column type with the same results.

Is there a way to achieve correct result? I cannot change default timezone in the the whole application to UTC.

Thanks for your help

Answer 1

Your problem is that the date which the client sends to Oracle is already infected with the timezone and therefore, Oracle already gets the wrong data. You must find a way to create UTC times in all the places everywhere in your code before you send them to the database.

See this answer for code how to create a GMT date from a date in the local TZ. In my own code, I store the GMT date and the timezone. This allows me to compare the dates and to display them in local time, too. Otherwise, the user will be confused why he worked from 17:00 to 01:00 (TZ+0800) yesterday.

This article might also help: Write Time Zone Aware Code in Oracle

Answer 2

To set a timestamp value in a PreparedStatement in UTC timezone one should use

stmt.setTimestamp(1, t, TimeZone.getTimeZone("UTC"))

The Timestamp value is always UTC, but not always the jdbc driver can automatically sent it correctly to the server. The third, Calendar, parameter helps the driver to correctly prepare the value for the server.