Re: JDBC setTimestamp question

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(at)postgres(dot)rocks>
wrote:

>
>
> On Thu, 26 Jan 2023 at 04:57, arons <arons7(at)gmail(dot)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.
>> 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
>>
>>
>> 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.
>
> there is a PR correct mapping for postgres timestamptz type to sql type
> TIMESTAMP_W… by lopata2 · Pull Request #2715 · pgjdbc/pgjdbc (github.com)
> <https://github.com/pgjdbc/pgjdbc/pull/2715> which should address this
> issue in the future
>
> Regards,
>
> Dave
>