Re: JDBC setTimestamp question - Mailing list pgsql-jdbc

From arons
Subject Re: JDBC setTimestamp question
Date
Msg-id CA+XOKQDDLn7RjX_i3s5=xZH6L3PSQCK83pB3CNgPYBr0uikBmw@mail.gmail.com
Whole thread Raw
In response to Re: JDBC setTimestamp question  (Dave Cramer <davecramer@postgres.rocks>)
List pgsql-jdbc
Thanks a lot for the info.
Looking forward for that, in the mean time we will work with a patch.

BR
Renzo

On Thu, Jan 26, 2023 at 2:15 PM Dave Cramer <davecramer@postgres.rocks> wrote:


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.

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.
" 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.


Regards,

Dave

pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: JDBC setTimestamp question
Next
From: Dave Cramer
Date:
Subject: [pgjdbc/pgjdbc] f51c68: Make sure all configuration settins have defaults ...