From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Cc: | bristleconeweb(at)gmail(dot)com |
Subject: | timestamp with time zone ~> GMT |
Date: | 2025-01-27 07:51:00 |
Message-ID: | 173796426022.1064.9135167366862649513@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2025-01-27 08:51:08 | Re: timestamp with time zone ~> GMT |
Previous Message | Tom Lane | 2025-01-23 19:49:17 | Re: https://www.postgresql.org/docs/current/kernel-resources.html |