Re: timestamp with time zone ~> GMT - Mailing list pgsql-docs

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