agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: Laurenz Albe <laurenz.albe@cybertec.at>
To: bristleconeweb@gmail.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: timestamp with time zone ~> GMT
Date: Mon, 27 Jan 2025 09:51:08 +0100
Message-ID: <7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at> (raw)
In-Reply-To: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
References: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/17/datatype-datetime.html
> Description:
>
> Thank you for postgres. I wanted to offer clarification would may help
> others in the docs on time stamps (after discovering subtle issues have
> significant impact for me)
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS
>
> "An input value that has an explicit time zone specified is converted to
> UTC"
> "When a timestamp with time zone value is output, it is always converted
> from UTC to the current timezone zone"
>
> After re-testing behavior, it appears this means:
> 1. input DROPS the offset after conversion to UTC
> 2. output is system time or according to settings (DROPS utc and original
> time zone)
>
> To help illustrate the dilemma: consider an example use case where an
> airline is emailing flight departure and arrival times. Passengers typically
> need to know the times relative to the departure and destination time zones.
> Passengers would be confused to see all times according to their current
> time zone (which may be entirely different from the time zones of the
> flight). Additionally, iCal must know both time zones to determine the true
> flight time and render an accurate calendar.
>
> Suggestion: Assuming my understanding is accurate - clarify for the reader
> that time zone offset is lost (after conversion to UTC). At risk of stating
> the obvious: "timestamp with time zone" is a rather misleading name.
> "timestamp coerced to UTC" or something would be more accurate.
>
> Since timestamp with time zone doesn't record the input time zone, there is
> an associated issue: how to record the input time zone. I'm unable to locate
> a recommendation through postgres docs. Certainly text or similar would
> "work" for IANA time zones... however it would be helpful to have a little
> more guidance, such as validation to the enum
> https://www.postgresql.org/docs/17/view-pg-timezone-names.html I considered
> using "time with time zone" but I see this is also coerced to UTC.
>
> Hopefully these suggestions are helpful. Thanks again!
Your understanding is correct.
I personally think of "timestamp with time zone" as an "absolute timestamp".
To preserve the original time zone that was entered, you'd have to store it
in a separate database column.
We welcome a documentation patch!
Yours,
Laurenz Albe
view thread (12+ messages) latest in thread
Message-ID: <7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at>
Permalink: ../7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at/
Also on: postgresql.org/message-id/7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, bristleconeweb@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: timestamp with time zone ~> GMT
In-Reply-To: <7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox