BUG #16060: JDBC - badly gets DateOffsetTime value from database

On Tue, Oct 15, 2019 at 06:52:12PM +0000, PG Bug reporting form wrote:

Java DateOffsetTime value correctly stored to database to timestamptz. I see
right conversion to string including right time zone.
Opposite process doesn't get the right DateOffsetTime value from database.
In my case the string representation stored to database is 2019-10-15
20:26:41.391055+02 but I get 2019-10-15T18:26:41.391055Z which doesn't by +2
hours which is my time zone.
I think the bug is in TimestampUtils line 513:
// Postgres is always UTC
OffsetDateTime result = OffsetDateTime.of(ts.year, ts.month, ts.day,
ts.hour, ts.minute, ts.second, ts.nanos, zoneOffset)
.withOffsetSameInstant(ZoneOffset.UTC);
The last line ".withOffsetSameInstant(ZoneOffset.UTC);" is the bug

As an issue for the Postgres JDBC driver, I think that you should
either contact pgsql-bugs:
https://www.postgresql.org/list/pgsql-jdbc/
Or raise an issue where the project is located:
https://github.com/pgjdbc/pgjdbc

Thanks,
--
Michael

On Wed, 16 Oct 2019 at 06:20, Michael Paquier <michael@paquier.xyz> wrote:

On Tue, Oct 15, 2019 at 06:52:12PM +0000, PG Bug reporting form wrote:

Java DateOffsetTime value correctly stored to database to timestamptz. I

see

right conversion to string including right time zone.
Opposite process doesn't get the right DateOffsetTime value from

database.

In my case the string representation stored to database is 2019-10-15
20:26:41.391055+02 but I get 2019-10-15T18:26:41.391055Z which doesn't

by +2

hours which is my time zone.
I think the bug is in TimestampUtils line 513:
// Postgres is always UTC
OffsetDateTime result = OffsetDateTime.of(ts.year, ts.month, ts.day,
ts.hour, ts.minute, ts.second, ts.nanos, zoneOffset)
.withOffsetSameInstant(ZoneOffset.UTC);
The last line ".withOffsetSameInstant(ZoneOffset.UTC);" is the bug

As an issue for the Postgres JDBC driver, I think that you should
either contact pgsql-bugs:
https://www.postgresql.org/list/pgsql-jdbc/
Or raise an issue where the project is located:
https://github.com/pgjdbc/pgjdbc

On Tue, 15 Oct 2019 at 20:53, PG Bug reporting form <noreply@postgresql.org>
wrote:

The following bug has been logged on the website:

Bug reference: 16060
Logged by: Jan Marencik
Email address: stan@marencik.cz
PostgreSQL version: 12.0
Operating system: Ubuntu
Description:

postgresql 42.2.8

Java DateOffsetTime value correctly stored to database to timestamptz. I
see
right conversion to string including right time zone.
Opposite process doesn't get the right DateOffsetTime value from database.
In my case the string representation stored to database is 2019-10-15
20:26:41.391055+02 but I get 2019-10-15T18:26:41.391055Z which doesn't by
+2
hours which is my time zone.
I think the bug is in TimestampUtils line 513:
// Postgres is always UTC
OffsetDateTime result = OffsetDateTime.of(ts.year, ts.month, ts.day,
ts.hour, ts.minute, ts.second, ts.nanos, zoneOffset)
.withOffsetSameInstant(ZoneOffset.UTC);
The last line ".withOffsetSameInstant(ZoneOffset.UTC);" is the bug

Are you aware that PostgreSQL does not actually store the timezone in the
database?
The timestamp is store in the the database in UTC and when you retrieve it
the timezone is applied.
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

"For timestamp with time zone, the internally stored value is always in UTC
(Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).
An input value that has an explicit time zone specified is converted to UTC
using the appropriate offset for that time zone. If no time zone is stated
in the input string, then it is assumed to be in the time zone indicated by
the system's TimeZone
<https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE&gt;
parameter,
and is converted to UTC using the offset for the timezone zone."

There is little the driver can do in the case above other than provide you
with UTC. The timestamp is correct it is just not set to your timezone.

If you want that you should be using LocalDateTime

Thanks,

Dave

This was a bug created in https://www.postgresql.org/list/pgsql-jdbc/

I haven't an account on github so much easier for me will be to make a fork and fix myself...

Thank you,

Jan

16.10.2019, 06:20, "Michael Paquier" <michael@paquier.xyz>: