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

Поиск
Список
Период
Сортировка
От Dave Cramer
Тема Re: BUG #16060: JDBC - badly gets DateOffsetTime value from database
Дата
Msg-id CADK3HHKUoGpg1Obio5_Uo5w_BL8WDg-N0mwAzZT0kDGRZ_+7sA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #16060: JDBC - badly gets DateOffsetTime value from database  (Michael Paquier <michael@paquier.xyz>)
Список pgsql-bugs
Дерево обсуждения



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 

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: BUG #16058: show session_user shows a not clear error message
Следующее
От: Thomas Munro
Дата:
Сообщение: Re: ERROR: multixact X from before cutoff Y found to be still running