Re: JDBC setTimestamp question - Mailing list pgsql-jdbc

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@gmail.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