Обсуждение: BUG #16060: JDBC - badly gets DateOffsetTime value from database
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
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.
"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 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>: > 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