Subject: JDBC setTimestamp question

From: arons <[email-masked]>
Date: Tue, 24 Jan 2023 14:52:29 +0100

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

---

From: arons <[email-masked]>
Date: Wed, 25 Jan 2023 14:04:39 +0100

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-masked]> wrote:

>         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"));            }        }
>
>
>
>         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));            }        }
>
>

---

From: Dave Cramer <[email-masked]>
Date: Wed, 25 Jan 2023 10:07:02 -0500

On Wed, 25 Jan 2023 at 08:05, arons <[email-masked]> wrote:

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

---

From: arons <[email-masked]>
Date: Wed, 25 Jan 2023 16:14:36 +0100

I checked the source code and inside the method
org.postgresql.jdbc.PgPreparedStatement.setTimestamp(int, Timestamp,
Calendar) I found the code I was interesting to:

int oid = Oid.UNSPECIFIED;

// Use UNSPECIFIED as a compromise to get both TIMESTAMP and TIMESTAMPTZ
working.

// This is because you get this in a +1300 timezone:

//

// template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;

// timestamptz

// ------------------------

// 2005-01-01 18:00:00+13

// (1 row)

// template1=# select '2005-01-01 15:00:00 +1000'::timestamp;

// timestamp

// ---------------------

// 2005-01-01 15:00:00

// (1 row)

// template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;

// timestamp

// ---------------------

// 2005-01-01 18:00:00

// (1 row)

// So we want to avoid doing a timestamptz -> timestamp conversion, as that

// will first convert the timestamptz to an equivalent time in the server's

// timezone (+1300, above), then turn it into a timestamp with the "wrong"

// time compared to the string we originally provided. But going straight

// to timestamp is OK as the input parser for timestamp just throws away

// the timezone part entirely. Since we don't know ahead of time what type

// we're actually dealing with, UNSPECIFIED seems the lesser evil, even if
it

// does give more scope for type-mismatch errors being silently hidden.

// If a PGTimestamp is used, we can define the OID explicitly.

if (t instanceof PGTimestamp) {

PGTimestamp pgTimestamp = (PGTimestamp) t;

if (pgTimestamp.getCalendar() == null) {

oid = Oid.TIMESTAMP;

} else {

oid = Oid.TIMESTAMPTZ;

cal = pgTimestamp.getCalendar();

}

}

if (cal == null) {

cal = getDefaultCalendar();

}

bindString(i, getTimestampUtils().toString(cal, t), oid);

I saw that I can use PGTimestamp instead of sql Timestamp.

In that case all works fine.

Anyway I do not fully understand the comment and why we set oid = Oid.
UNSPECIFIED; in case of normal sql Timestamp.

Is there any different between a java.sql.Timestamp.Timestamp and
org.postgresql.util.PGTimestamp.PGTimestamp ?

Cannot be use oid = Oid.TIMESTAMP in any other case as when t is not an
instance of PGTimestamp?

Thanks

Renzo

---

From: Dave Cramer <[email-masked]>
Date: Wed, 25 Jan 2023 10:19:05 -0500

> // template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;
We use unspecified because we don't know whether setTimestamp is setting a
timestamptz or a timestamp.

Dave

---

From: arons <[email-masked]>
Date: Thu, 26 Jan 2023 10:57:13 +0100

Well java Timestamp reflect the java Date which is intended to reflect
coordinated universal time (UTC) in most cases.
That is not the same in postgres with timestamp.
See also:
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

So in generally timestamptz is what you need.

JDBC api should in my opinion reflect that nature, so a call to
setTimestamp should reflect the that nature too.

Also in the last line bindString(i, getTimestampUtils().toString(cal, t),
oid) it calls:

public synchronized String toString(@Nullable Calendar cal, Timestamp x) {

return toString(cal, x, true);

}

public synchronized String toString(@Nullable Calendar cal, Timestamp x,
boolean withTimeZone) {

Where the timezone is correctly always passed.

In the current implementation if I do not force the conversion to
timestamptz immediately we lost the timezone...not really transparent to
the java user.
See also here: https://www.postgresql.org/docs/15/datatype-datetime.html
" PostgreSQL never examines the content of a literal string before
determining its type, and therefore will treat both of the above as timestamp
without time zone. To ensure that a literal is treated as timestamp with
time zone, give it the correct explicit type: ... "
That is the most scary to me, so without an explicit cast I can maybe lost
the timezone information even if I pass on java side.
Again an explicit cast timestamptz -> timestamp would do much more sense to
me instead of avoid it, that would be clear to the user.

In addition, the currently implementation force to write specific code or
specific query based on the the back-end DB.
In our software we are supporting different DB natures and I would expect
the same behavior on different DBs for the same simple java code and query,
which is not the case here.

Maybe the code can be reconsider.

Thanks
Renzo

On Wed, Jan 25, 2023 at 4:19 PM Dave Cramer <[email-masked]>
wrote:

>> // we're actually dealing with, UNSPECIFIED seems the lesser evil, even
>> if it
>>

---

From: Dave Cramer <[email-masked]>
Date: Thu, 26 Jan 2023 08:14:46 -0500

On Thu, 26 Jan 2023 at 04:57, arons <[email-masked]> wrote:

> Again an explicit cast timestamptz -> timestamp would do much more sense
> to me instead of avoid it, that would be clear to the user.
>
> Hi Renzo,

I feel your pain. The fact that postgres has two timestamps and java only
has one has caused more issues than any other single thing.

While applications can afford to be opinionated the driver cannot. We try
to please everyone, and in doing so sometimes please nobody.
What I mean by this is that if the user has decided they want to use
timestamp then we try to make that work. Similarly with timestamptz.

there is a PR correct mapping for postgres timestamptz type to sql type
TIMESTAMP_W… by lopata2 · Pull Request #2715 · pgjdbc/pgjdbc (github.com)
<https://github.com/pgjdbc/pgjdbc/pull/2715> which should address this
issue in the future

Regards,

Dave

---

From: arons <[email-masked]>
Date: Thu, 26 Jan 2023 15:57:20 +0100

Thanks a lot for the info.
Looking forward for that, in the mean time we will work with a patch.

BR
Renzo

On Thu, Jan 26, 2023 at 2:15 PM Dave Cramer <[email-masked]>
wrote:

>> That is the most scary to me, so without an explicit cast I can maybe
>> lost the timezone information even if I pass on java side.