Re: JDBC setTimestamp question - Mailing list pgsql-jdbc

From Dave Cramer
Subject Re: JDBC setTimestamp question
Date
Msg-id CADK3HHLHa_skyhu0wdCg5gGfMcnm6_cQNeF=eDiDRiPtLuaLUg@mail.gmail.com
Whole thread Raw
In response to Re: JDBC setTimestamp question  (arons <arons7@gmail.com>)
List pgsql-jdbc



On Wed, 25 Jan 2023 at 08:05, arons <arons7@gmail.com> wrote:
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

This isn't really surprising and has nothing to do with JDBC. If you tried this in psql you would get the same result. One thing to keep in mind is that JDBC uses prepared statements so you see the following:

prepare foo as select * from  test  where create_dt < $1  + interval '1 day';
ERROR:  operator does not exist: timestamp with time zone < interval
LINE 1: ...epare foo as select * from  test  where create_dt < $1  + in...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
test=# prepare foo as select * from  test  where create_dt < $1::timestamptz  + interval '1 day';

Does that help ?

Dave

pgsql-jdbc by date:

Previous
From: arons
Date:
Subject: Re: JDBC setTimestamp question
Next
From: arons
Date:
Subject: Re: JDBC setTimestamp question