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

Timestamp 值始终为 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屋！