Re: JDBC setTimestamp question

I saw the code is not well formatted, at least if you read from the web
site of the mailing list.
The source code you can download here:
https://kdani.ch/share/pg/jdbc/TestTimestamptz.java.
How do you generally format code on the mailing list?
Thanks

On Tue, Jan 24, 2023 at 2:52 PM arons <arons7(at)gmail(dot)com> wrote:

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