Thread: 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@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

On Wed, 25 Jan 2023 at 10:15, arons <arons7@gmail.com> wrote:

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

We use unspecified because we don't know whether setTimestamp is setting a timestamptz or a timestamp. 

Dave

On Thu, 26 Jan 2023 at 04:57, arons <arons7@gmail.com> wrote:

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

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) which should address this issue in the future

Regards,

Dave