Re: JDBC setTimestamp question - Mailing list pgsql-jdbc
| From | arons |
|---|---|
| Subject | Re: JDBC setTimestamp question |
| Date | |
| Msg-id | CA+XOKQC67VVYk6HfPbFxWYDuwR4BmVzhWhBC=La1y3jmYNta2Q@mail.gmail.com Whole thread Raw |
| In response to | Re: JDBC setTimestamp question (Dave Cramer <davecramer@postgres.rocks>) |
| Responses |
Re: JDBC setTimestamp question
(Dave Cramer <davecramer@postgres.rocks>)
|
| List | pgsql-jdbc |
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) {
timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type: ... "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
pgsql-jdbc by date: