Thread: timestamp with time zone ~> GMT
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!
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
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
>> 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.
> Your understanding is correct.
> I personally think of "timestamp with time zone" as an "absolute timestamp".
Yes. The datatype's behavior is not what you would expect from the
SQL standard, which makes our choice of the standard-derived name
rather unfortunate. That choice is well over 25 years old though,
so there's not much chance of changing it now.
> To preserve the original time zone that was entered, you'd have to store it
> in a separate database column.
The other problem is: what are you gonna store exactly? A numeric
offset from UTC is unambiguous but doesn't bring much to the table
compared to what we do now. A time zone name is a possibility,
but (a) that's bulky and (b) the politicians keep changing the
DST laws, so the meaning could change. In certain cases like
appointment calendars, tracking local law is just what you want
... but in cases like flight schedules, probably not.
regards, tom lane
Thank you. I believe that explanation would have helped.
FWIW, I ultimately decided to simply duplicate the column:
1. keep the timestamp column for sorting/filters/etc.
2. add a full iso 8601 column as text for straightforward client handling (avoids forcing the client to stitch the data back together; simplicity preferred over small data cost IMO).
Perhaps this would be a preferred solution for many others' use cases? Certainly up to you all on whether this additional tip is worth including. I'll just say I'm a bit embarrassed to admit how long it took me to realize this answer!
On Mon, Feb 3, 2025 at 10:59 AM Robert Treat <rob@xzilla.net> wrote:
On Mon, Jan 27, 2025 at 9:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
> >> 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.
>
> > Your understanding is correct.
> > I personally think of "timestamp with time zone" as an "absolute timestamp".
>
> Yes. The datatype's behavior is not what you would expect from the
> SQL standard, which makes our choice of the standard-derived name
> rather unfortunate. That choice is well over 25 years old though,
> so there's not much chance of changing it now.
>
This does seem to come up often enough that it probably is worth being
a bit more explicit about how this works; attached patch attempts
that.
Note, I dropped the bit about GMT; that change was made ~40 years ago,
and I suspect it is close to noise for many people these days, though
it could be added back if folks feel strongly about it.
> > To preserve the original time zone that was entered, you'd have to store it
> > in a separate database column.
>
> The other problem is: what are you gonna store exactly? A numeric
> offset from UTC is unambiguous but doesn't bring much to the table
> compared to what we do now. A time zone name is a possibility,
> but (a) that's bulky and (b) the politicians keep changing the
> DST laws, so the meaning could change. In certain cases like
> appointment calendars, tracking local law is just what you want
> ... but in cases like flight schedules, probably not.
>
As Tom notes above, what to store is debatable, and generally seems to
conflate storage and display desires together, which makes it hard to
imagine a generic enough implementation to put into core, but there
are some attempts to solve this problem floating around in extension
land. See https://github.com/mweber26/timestampandtz/blob/master/README.md
as one such attempt.
Robert Treat
https://xzilla.net
On Mon, 3 Feb 2025 at 11:21, Álvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2025-Feb-03, Robert Treat wrote:
> This does seem to come up often enough that it probably is worth being
> a bit more explicit about how this works; attached patch attempts
> that.
LGTM.
> Note, I dropped the bit about GMT; that change was made ~40 years ago,
> and I suspect it is close to noise for many people these days, though
> it could be added back if folks feel strongly about it.
I don't feel strongly about it, but another option might be to add a
<footnote> so that it is still there but less intrusive. Grepping for
GMT in the postgres repo there are still over 1400 matches of all kinds.
> As Tom notes above, what to store is debatable, and generally seems to
> conflate storage and display desires together, which makes it hard to
> imagine a generic enough implementation to put into core, but there
> are some attempts to solve this problem floating around in extension
> land. See https://github.com/mweber26/timestampandtz/blob/master/README.md
> as one such attempt.
Another simple option is to store a timezone name in a separate column,
which you can then use in the AT TIME ZONE clause. This is of course
more wasteful than storing a 2-byte zone identifier as the
timestampandtz extension does.
If anything I'd advocate creating a type which stored both. Storing the TZ in another column would complicate things if the data were to be sent in binary.
Is there a case where we would send two attributes for one in binary ?
Dave
On Mon, Feb 3, 2025 at 12:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org> writes: > > On 2025-Feb-03, Robert Treat wrote: > >> This does seem to come up often enough that it probably is worth being > >> a bit more explicit about how this works; attached patch attempts > >> that. > > > LGTM. > > Hmm, I kind of like the up-front statement that timestamptz stores > UTC. How about this simpler change? > > diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml > index 1d9127e94e..269809dc81 100644 > --- a/doc/src/sgml/datatype.sgml > +++ b/doc/src/sgml/datatype.sgml > @@ -2263,6 +2263,8 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' > then it is assumed to be in the time zone indicated by the system's > <xref linkend="guc-timezone"/> parameter, and is converted to UTC using the > offset for the <varname>timezone</varname> zone. > + In either case, the originally stated or assumed time zone is not > + retained. > </para> I thought the re-order made sense since the preceding paragraph talks exclusively about behavior, so this paragraph first contrasts the behavioral difference between the two, and then mentions the storage aspects as part of that story. I actually like the above as well, but if it were me I'd move all mentions of storage (the existing + the above) to the end of the paragraph after the behavior aspects. > >> Note, I dropped the bit about GMT; that change was made ~40 years ago, > >> and I suspect it is close to noise for many people these days, though > >> it could be added back if folks feel strongly about it. > > > I don't feel strongly about it, but another option might be to add a > > <footnote> so that it is still there but less intrusive. Grepping for > > GMT in the postgres repo there are still over 1400 matches of all kinds. > > I think we'd better not remove the gloss for GMT just yet. It's still > the magic boot value for the timezone GUC for example (cf pgtz.c), > and it's still embedded in the IANA timezone database: > > $ ls /usr/share/zoneinfo/Etc > GMT GMT+11 GMT+4 GMT+8 GMT-10 GMT-14 GMT-5 GMT-9 UTC > GMT+0 GMT+12 GMT+5 GMT+9 GMT-11 GMT-2 GMT-6 GMT0 Universal > GMT+1 GMT+2 GMT+6 GMT-0 GMT-12 GMT-3 GMT-7 Greenwich Zulu > GMT+10 GMT+3 GMT+7 GMT-1 GMT-13 GMT-4 GMT-8 UCT > > Maybe we could move the info to the Glossary, but that seems like > a separate matter from what's under discussion here. > Yeah, I think for end users this isn't compelling, but I figured there might be some push back :-) Robert Treat https://xzilla.net
Robert Treat <rob@xzilla.net> writes:
> On Mon, Feb 3, 2025 at 12:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm, I kind of like the up-front statement that timestamptz stores
>> UTC. How about this simpler change?
> I thought the re-order made sense since the preceding paragraph talks
> exclusively about behavior, so this paragraph first contrasts the
> behavioral difference between the two, and then mentions the storage
> aspects as part of that story.
> I actually like the above as well, but if it were me I'd move all
> mentions of storage (the existing + the above) to the end of the
> paragraph after the behavior aspects.
OK, it makes more sense when considering the previous para as well.
Here's a combined proposal that also adds glossary entries.
regards, tom lane
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 1d9127e94e..b20241feb5 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2245,24 +2245,27 @@ TIMESTAMP '2004-10-19 10:23:54+02'
<programlisting>
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
</programlisting>
+ </para>
- In a literal that has been determined to be <type>timestamp without time
+ <para>
+ In a value that has been determined to be <type>timestamp without time
zone</type>, <productname>PostgreSQL</productname> will silently ignore
any time zone indication.
That is, the resulting value is derived from the date/time
- fields in the input value, and is not adjusted for time zone.
+ fields in the input string, and is not adjusted for time zone.
</para>
<para>
- For <type>timestamp with time zone</type>, the internally stored
- value is always in UTC (Universal
- Coordinated Time, traditionally known as Greenwich Mean Time,
- <acronym>GMT</acronym>). An input value that has an explicit
- time zone specified is converted to UTC using the appropriate offset
+ For <type>timestamp with time zone</type> values, an input string
+ that includes an explicit time zone will be converted to UTC
+ (<glossterm linkend="glossary-utc">Universal Coordinated
+ Time</glossterm>) using the appropriate offset
for that time zone. If no time zone is stated in the input string,
then it is assumed to be in the time zone indicated by the system's
<xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
offset for the <varname>timezone</varname> zone.
+ In either case, the value is stored internally as UTC, and the
+ originally stated or assumed time zone is not retained.
</para>
<para>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index f54f25c1c6..c0f812e3f5 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -851,6 +851,11 @@
</glossdef>
</glossentry>
+ <glossentry>
+ <glossterm>GMT</glossterm>
+ <glosssee otherterm="glossary-utc" />
+ </glossentry>
+
<glossentry id="glossary-grant">
<glossterm>Grant</glossterm>
<glossdef>
@@ -2047,6 +2052,17 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-utc">
+ <glossterm>UTC</glossterm>
+ <glossdef>
+ <para>
+ Universal Coordinated Time, the primary global time reference,
+ approximately the time prevailing at the zero meridian of longitude.
+ Often but inaccurately referred to as GMT (Greenwich Mean Time).
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-vacuum">
<glossterm>Vacuum</glossterm>
<glossdef>
Robert Treat <rob@xzilla.net> writes:
> On Thu, Feb 6, 2025 at 5:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Here's a combined proposal that also adds glossary entries.
> +1
OK, pushed.
regards, tom lane