oracle jdbc中的PreparedStatement和setTimestamp [英] PreparedStatement and setTimestamp in oracle jdbc

问题描述

我在where子句中使用带有时间戳的PreparedStatement：

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"));

当我在客户端计算机上有不同的时区时，我得到的结果是不同的。这是Oracle jdbc中的错误吗？或正确的行为？

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数据库版本是10.2，我已经尝试使用oracle jdbc瘦驱动程序版本10.2和11.1。

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

参数是Timestamp，我预计在途中不会进行任何时间转换。数据库列类型是DATE，但我也使用TIMESTAMP列类型检查它，结果相同。

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.

有没有办法实现正确的结果？我无法将整个应用程序中的默认时区更改为UTC。

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

感谢您的帮助

推荐答案

要在UTC时区的PreparedStatement中设置时间戳值，应该使用

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

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

时间戳值是始终是UTC，但并不总是jdbc驱动程序可以自动将其正确发送到服务器。第三个Calendar参数可以帮助驱动程序正确地为服务器准备值。

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.

这篇关于oracle jdbc中的PreparedStatement和setTimestamp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！