Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pKhYi-0006JM-LN for pgsql-jdbc@arkaria.postgresql.org; Wed, 25 Jan 2023 15:19:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pKhYh-0005GL-H4 for pgsql-jdbc@arkaria.postgresql.org; Wed, 25 Jan 2023 15:19:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pKhYh-0005GB-1q for pgsql-jdbc@lists.postgresql.org; Wed, 25 Jan 2023 15:19:31 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pKhYc-00032h-SU for pgsql-jdbc@lists.postgresql.org; Wed, 25 Jan 2023 15:19:30 +0000 Received: from mail-ua1-f50.google.com (mail-ua1-f50.google.com [209.85.222.50]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id D702120042 for ; Wed, 25 Jan 2023 10:19:22 -0500 (EST) Received: by mail-ua1-f50.google.com with SMTP id r10so1822984ual.3 for ; Wed, 25 Jan 2023 07:19:22 -0800 (PST) X-Gm-Message-State: AO0yUKXu9r+6VJBImZoiQq7s9frFXB4aGoMJT3drpYNg9e4pDJGS3yDW k3F5gx+6rZkLGVoMBscfsgurio0nJh+agNF85Gs= X-Google-Smtp-Source: AK7set8YCdYNH9i4VcIA4q58DHqwBsZ3G7BLL8LJ1mQqfAMF3DzXnhsJgGNNG3gonIqXAKmmrZRwyzpGu73wrSFlJcw= X-Received: by 2002:ab0:5ac8:0:b0:654:6fdf:9550 with SMTP id x8-20020ab05ac8000000b006546fdf9550mr730492uae.102.1674659962058; Wed, 25 Jan 2023 07:19:22 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Dave Cramer Date: Wed, 25 Jan 2023 10:19:05 -0500 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: JDBC setTimestamp question To: arons Cc: pgsql-jdbc@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000287e9f05f318287a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000287e9f05f318287a Content-Type: text/plain; charset="UTF-8" On Wed, 25 Jan 2023 at 10:15, arons 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 --000000000000287e9f05f318287a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable



On Wed, 25 Jan 2023 at 10:15, aro= ns <arons7@gmail.com> wrote:<= br>
I checked the source code and inside the method org.postgresql.jdbc.P= gPreparedStatement.setTimestamp(int, Timestamp, Calendar) I found the code = I was interesting to:

int oid =3D Oid.UNSPECIFIED;

// Use UNSPECIFIED as a compromise to get both TIMESTAMP and TIMES= TAMPTZ working.

// This is because you get this in a +1300 timezon= e:

//

// template1=3D# select '2005-01-01 15:00:00 +1000'::timestamptz;<= /p>

// timestamptz

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

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

// (1 row)

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

// timestamp

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

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

// (1 row)

// template1=3D# 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<= span style=3D"color:rgb(63,127,95)"> -> 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<= span style=3D"color:rgb(63,127,95)"> with the "wrong"

// time compared to the string we originally provided. But going s= traight

// 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 e= vil, even if it

// does give more scope for type-mismatch errors being silently hi= dden.

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

if (t instanceof= PGTimestamp) {

PGTimestamp pgTimestamp = =3D (PGTimestamp) t;

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

oid =3D Oid.TIMESTAM= P;

} else {=

oid =3D Oid.TIMESTAM= PTZ;

cal =3D pgTimestamp.getCalendar();

}

}

if (cal =3D=3D n= ull) {

cal =3D getDefaultCalend= ar();

}

bindString(i, getTimestamp= Utils().toString(cal, = t), oid);




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

In that case = all works fine.

Anyway I = do not fully understand the comment and why we set oid =3D Oid.UNSPECIF= IED; in case of normal sql T= imestamp.

Is there any differ= ent between a java.sql.Timestamp.Timestamp and org.postgresql.util.PGTimest= amp.PGTimestamp ?

Cannot be u= se oid =3D=20 Oid.TIMESTAMP in any other case a= s when=20 t is not an instance of PGTimestamp?


Than= ks

Renzo




We use unspecified because we= don't know whether setTimestamp is setting a timestamptz or a timestam= p.=C2=A0

Dave
--000000000000287e9f05f318287a--