PreparedStatement and setTimestamp in oracle jdbc

问题:

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

回答1:

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

stmt.setTimestamp(1, t, Calendar.getInstance(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.