开发者

PreparedStatement and setTimestamp in oracle jdbc

开发者 https://www.devze.com 2022-12-30 12:27 出处:网络
I am using PreparedStatement with Timestamp in where clause: PreparedStatement s=c.prepareStatement(\"select value,utctimestamp from t where utctimestamp>=? and utctimestamp<?\");

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开发者_JAVA技巧 = 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


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号