Time and timezone issues in the driver and the driver documentation #3281
stickfigure
started this conversation in
General
Replies: 1 comment
-
On Mon, 10 Jun 2024 at 20:11, Jeff Schnitzer ***@***.***> wrote:
Between #3228 <#3228> and the
docs, I notice a few misconceptions. It's caused me some headache here in a
large codebase I inherited, hoping we can sort it out.
The issue is pretty much summarized by this line in the documentation:
ZonedDateTime, Instant and OffsetTime / TIME WITH TIME ZONE are not supported.
Also note that all OffsetDateTime instances will have be in UTC (have offset 0).
This is because the backend stores them as UTC.
A little background:
- Oracle has a type TIMESTAMP WITH TIME ZONE which actually stores
timezone information. If you save 2024-06-10T12:00:00-8:00, you get
the same pacific-time value back when you query.
- The folks that wrote "tables B-4 and B-5 of the JDBC 4.2
specification" had this in mind when they made those tables.
- Postgres has a type TIMESTAMP WITH TIME ZONE. It is very different
from the Oracle type.
- Postgres does *not* store a timezone.
- Postgres stores an elapsed time since epoch. It's instant-type
data, Just like java.util.Date, java.time.Instant,
java.sql.Timestamp, and javascript Date.
- As an interpretation of "WITH TIME ZONE", Postgres treats
timestamps (for purposes of date math) as being in the timezone of the
*session*. Which you can change with database defaults, with user
defaults, or with plain old SET timezone TO 'Somewhere/Else'.
So the problems:
- Postgres does not store TIMESTAMP WITH TIME ZONE in UTC. It doesn't
store timestamps in any timezone. It stores microseconds-since-epoch, and
epoch doesn't have a timezone. Postgres stores microseconds since Jan 1,
1970, 6am at UTC+6. Or maybe it's midnight UTC+0. It makes no difference.
What it does is take the timestamp and timezone information and store a
value which represents the time at UTC which is effectively storing it at
UTC
From the official docs
```
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.
```
Dave
… Message ID: ***@***.***>
|
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Between #3228 and the docs, I notice a few misconceptions. It's caused me some headache here in a large codebase I inherited, hoping we can sort it out.
The issue is pretty much summarized by this line in the documentation:
A little background:
TIMESTAMP WITH TIME ZONE
which actually stores timezone information. If you save2024-06-10T12:00:00-8:00
, you get the same pacific-time value back when you query.TIMESTAMP WITH TIME ZONE
. It is very different from the Oracle type.java.util.Date
,java.time.Instant
,java.sql.Timestamp
, and javascriptDate
.SET timezone TO 'Somewhere/Else'
.So the problems:
TIMESTAMP WITH TIME ZONE
in UTC. It doesn't store timestamps in any timezone. It stores microseconds-since-epoch, and epoch doesn't have a timezone. Postgres stores microseconds since Jan 1, 1970, 6am at UTC+6. Or maybe it's midnight UTC+0. It makes no difference.OffsetDateTime
is not the natural mapping of Postgres' column. Oracle yes, Postgres no; the types are not the same. The natural mapping of Postgres' timestamp column types isjava.time.Instant
orjava.util.Date
. That is, elapsed time since epoch.OffsetDateTime
orZonedDateTime
for aTIMESTAMP WITH TIMEZONE
from the driver, it should be sensitive to the session time zone. Hardcoding it to UTC is wrong by any possible interpretation of the postgres documentation.LocalDateTime
is not a natural mapping ofTIMESTAMP WITHOUT TIME ZONE
. The postgres column still stores elapsed-time-since-epoch, so it's an Instant type. Postgres doesn't have a realLocalDateTime
type.The only reason this hasn't been a major issue in the past is that most people (sanely) keep their production session timezone in UTC. But it still causes tests to fail when you create an OffsetDateTime on your local machine, roundtrip it to the database, and it's in a different timezone.
What I propose as fixes:
java.time.Instant
and discouragejava.time.OffsetDateTime
(unless someone wants to implement session-timezone-sensitivity).java.time.Instant
for both timestamp types. I haven't looked too deeply at the code, but I should be able to make a PR.Beta Was this translation helpful? Give feedback.
All reactions