public inbox for [email protected]  

help / color / mirror / Atom feed

From: arons <[email protected]>
To: [email protected]
Subject: Re: JDBC setTimestamp question
Date: Wed, 25 Jan 2023 14:04:39 +0100
Message-ID: <CA+XOKQCC-2cqTD42hftAsNdA6f2S7SiayyU9diWpVP6CaDxgKQ@mail.gmail.com> (raw)
In-Reply-To: <CA+XOKQCfjisonmMzyz_RM6Q-==sYoRiZDQ=1avmsg6ffUXeB4A@mail.gmail.com>
References: <CA+XOKQCfjisonmMzyz_RM6Q-==sYoRiZDQ=1avmsg6ffUXeB4A@mail.gmail.com>

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 <[email protected]> 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
>
>
>
>
>
>

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: JDBC setTimestamp question
  In-Reply-To: <CA+XOKQCC-2cqTD42hftAsNdA6f2S7SiayyU9diWpVP6CaDxgKQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox