Re: JDBC setTimestamp question

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

On Wed, Jan 25, 2023 at 2:04 PM arons <arons7(at)gmail(dot)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(at)gmail(dot)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
>>
>>
>>
>>
>>
>>