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

On Wed, 16 Oct 2019 at 06:20, Michael Paquier <michael(at)paquier(dot)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(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16060
> Logged by: Jan Marencik
> Email address: stan(at)marencik(dot)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>
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

>
>