Re: JDBC setTimestamp question - Mailing list pgsql-jdbc

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.

See also: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

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.

See also here: https://www.postgresql.org/docs/15/datatype-datetime.html

" 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