Re: JDBC setTimestamp question

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