JDBC setTimestamp question - Mailing list pgsql-jdbc
| From | arons |
|---|---|
| Subject | JDBC setTimestamp question |
| Date | |
| Msg-id | CA+XOKQCfjisonmMzyz_RM6Q-==sYoRiZDQ=1avmsg6ffUXeB4A@mail.gmail.com Whole thread Raw |
| Responses |
Re: JDBC setTimestamp question
(arons <arons7@gmail.com>)
|
| List | pgsql-jdbc |
Dear All,
playing with postgres jdbc and timestamptz I found some confusing stuff.
Here an example of the code:
conn.prepareStatement(" drop table if exists test ").executeUpdate(); conn.prepareStatement(" create table if not exists test(id bigint, pname text, create_dt timestamptz) ") .executeUpdate(); System.out.println("insert some data.."); conn.prepareStatement(" insert into test values( 1, 'hello', now() - interval '1 day' ) ").executeUpdate(); conn.prepareStatement(" insert into test values( 2, 'world', now() - interval '1 day' ) ").executeUpdate(); System.out.println("Executing query 01 ..."); try (PreparedStatement ps01 = conn.prepareStatement("select * from test where create_dt < ? ");) { ps01.setTimestamp(1, new Timestamp(System.currentTimeMillis()), null); ResultSet rs = ps01.executeQuery(); while (rs.next()) { System.out.println("query 01 id:" + rs.getInt("id")); } } System.out.println("Executing query 02 ..."); try (PreparedStatement ps02 = conn .prepareStatement("select * from test where create_dt < ? + interval '1 day' ");) { ps02.setTimestamp(1, new Timestamp(System.currentTimeMillis()), null); ResultSet rs = ps02.executeQuery(); while (rs.next()) { System.out.println("query 02 id:" + rs.getInt("id")); } }
the first select run without any problem, the second query instead:
select * from test where create_dt < ? + interval '1 day' gives this error:
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone < interval
My first question is, how is the proper way to set the parameter to be of type timestamptz? if I add cast(? as timestamptz) all work fine.
NOTE the following query run without problem in postgres: select * from test where create_dt < now() + interval '1 day'
So I expected the same from jdbc setting the paramter with method setTimestamp.
I've tried also the following:
System.out.println("Executing query 00 ..."); try (PreparedStatement ps02 = conn.prepareStatement(" select ? as tt ");) { ps02.setTimestamp(1, new Timestamp(System.currentTimeMillis()), null); ResultSet rs = ps02.executeQuery(); ResultSetMetaData metadata = rs.getMetaData(); System.out.println("column type : " + metadata.getColumnType(1)); System.out.println("varchar ? :" + java.sql.Types.VARCHAR); while (rs.next()) { System.out.println("tt:" + rs.getString(1)); System.out.println("tt:" + rs.getTimestamp(1)); } }
Why in this case the data type is set to varchar?
I was really surprise from the result.
That exaplain also to me why the query above give me the error. Some how the expression with varchar and interval results in a interval type?
How can I deal with those problems?
Is there any documentation abot jdbc set method and their result db types? I was not able to find.
Thanks for help
Renzo
pgsql-jdbc by date: