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?