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
JDBC API InterfaceStatement – Used to execute string-based SQL queries. PreparedStatement – Used to execute parameterized SQL queries.
More Detail. The PreparedStatement interface extends the Statement interface it represents a precompiled SQL statement which can be executed multiple times. This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.
The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database. They also define methods that help bridge data type differences between Java and SQL data types used in a database.
executeUpdate. Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT , UPDATE or DELETE ; or an SQL statement that returns nothing, such as a DDL statement.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With