Обсуждение: Java 8 OffsetDateTime clarification

In the JDBC document about Java 8 date and times, it says, "Also note 
that all OffsetDateTime instances will have be in UTC (have offset 0). 
This is because the backend stores them as UTC.

Ref: https://jdbc.postgresql.org/documentation/head/java8-date-time.html

This seems incorrect to me.  The backend, when using TIMESTAMP WITH TIME 
ZONE, stores the date+time+tzoffset, and they are not all 0 (for 
example, PSQL SELECT show such fields like "2019-04-18 
07:35:25.061-07".  And of course OffsetDateTime seems to align with 
TIMESTAMP WITH TIME ZONE.  Lastly, I seem to be able to 
ResultSet.getObject() and PreparedStatement.setObject() with 
OffsetDateTime param without them all having offset 0 (I don't force the 
OffsetDateTime to be in the UTC zone), and they look correct.

Can you clarify if that statement in the documentation is correct?  I 
want to make sure I code this correctly as I'm updating from the older 
use of java.sql.Timestamp for this purpose.

Thanks,
David

David Wall schrieb am 07.05.2019 um 04:31:
> This seems incorrect to me.  
> The backend, when using TIMESTAMP WITH TIME ZONE, stores the date+time+tzoffset

No, it does not.

From https://www.postgresql.org/docs/current/datatype-datetime.html

> 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.
> 
> When a timestamp with time zone value is output, it is always
> converted from UTC to the current timezone zone, and displayed as
> local time in that zone

On 5/6/19 11:11 PM, Thomas Kellerer wrote:

David Wall schrieb am 07.05.2019 um 04:31:

This seems incorrect to me.
The backend, when using TIMESTAMP WITH TIME ZONE, stores the date+time+tzoffset

No, it does not.

 From https://www.postgresql.org/docs/current/datatype-datetime.html

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.

When a timestamp with time zone value is output, it is always
converted from UTC to the current timezone zone, and displayed as
local time in that zone

Okay, yes, that's true.  I shouldn't have used "store" as in what's written to disk as that's not of interest to most users of a DB, other then we expect to be able to order them in a meaningful way.

What I meant is that if we present it a date+time+tzoffset to JDBC, it stores the correct date and time in the DB, not one off by the specified tzoffset.

I don't change my OffsetDateTime variables to UTC before giving them to JDBC, and the timestamps are stored and retrieved as expected.

This challenges the notion that "Also note that all OffsetDateTime instances will have be in UTC (have offset 0)." is true, or am I just getting unexpectedly normal results of PG without giving OffsetDateTime instances in UTC?