agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: bristleconeweb@gmail.com
Subject: timestamp with time zone ~> GMT
Date: Mon, 27 Jan 2025 07:51:00 +0000
Message-ID: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org> (raw)
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!
view thread (12+ messages) latest in thread
Message-ID: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
Permalink: ../173796426022.1064.9135167366862649513@wrigleys.postgresql.org/
Also on: postgresql.org/message-id/173796426022.1064.9135167366862649513@wrigleys.postgresql.org
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: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, bristleconeweb@gmail.com
Subject: Re: timestamp with time zone ~> GMT
In-Reply-To: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
* 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