Message-ID: <7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at>
Subject: Re: timestamp with time zone ~> GMT
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: bristleconeweb@gmail.com, pgsql-docs@lists.postgresql.org
Date: Mon, 27 Jan 2025 09:51:08 +0100
In-Reply-To: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
References: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>=20
> Page: https://www.postgresql.org/docs/17/datatype-datetime.html
> Description:
>=20
> 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-D=
ATETIME-INPUT-TIME-STAMPS
>=20
> "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"
>=20
> 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)
>=20
> To help illustrate the dilemma: consider an example use case where an
> airline is emailing flight departure and arrival times. Passengers typica=
lly
> need to know the times relative to the departure and destination time zon=
es.
> 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 tr=
ue
> flight time and render an accurate calendar.=20
>=20
> Suggestion: Assuming my understanding is accurate - clarify for the reade=
r
> that time zone offset is lost (after conversion to UTC). At risk of stati=
ng
> the obvious: "timestamp with time zone" is a rather misleading name.
> "timestamp coerced to UTC"  or something would be more accurate.
>=20
> 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 loc=
ate
> a recommendation through postgres docs. Certainly text or similar would
> "work" for IANA time zones... however it would be helpful to have a littl=
e
> more guidance, such as validation to the enum
> https://www.postgresql.org/docs/17/view-pg-timezone-names.html I consider=
ed
> using "time with time zone" but I see this is also coerced to UTC.
>=20
> 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