Horton

Thread

Commits

  1. Doc: clarify behavior of timestamptz input some more.

  1. timestamp with time zone ~> GMT

    The Post Office <noreply@postgresql.org> — 2025-01-27T07:51:00Z

    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!

  2. Re: timestamp with time zone ~> GMT

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-01-27T08:51:08Z

    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

  3. Re: timestamp with time zone ~> GMT

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-27T14:36:49Z

    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

  4. Re: timestamp with time zone ~> GMT

    Robert Treat <rob@xzilla.net> — 2025-02-03T15:59:29Z

    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

  5. Re: timestamp with time zone ~> GMT

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-02-03T16:21:41Z

    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.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos (bis) / con todos los humanos acabaré ¡acabaré! (Bender)

  6. Re: timestamp with time zone ~> GMT

    Jonathan Stanley <bristleconeweb@gmail.com> — 2025-02-03T16:50:57Z

    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
>

  7. Re: timestamp with time zone ~> GMT

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-03T17:23:07Z

    =?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>
 
      <para>

>> 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.

			regards, tom lane

  8. Re: timestamp with time zone ~> GMT

    Dave Cramer <davecramer@postgres.rocks> — 2025-02-03T17:50:05Z

    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

  9. Re: timestamp with time zone ~> GMT

    Robert Treat <rob@xzilla.net> — 2025-02-03T21:43:04Z

    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

  10. Re: timestamp with time zone ~> GMT

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-06T22:33:47Z

    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

  11. Re: timestamp with time zone ~> GMT

    Robert Treat <rob@xzilla.net> — 2025-02-07T15:50:20Z

    On Thu, Feb 6, 2025 at 5:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
>

+1

Robert Treat
https://xzilla.net

  12. Re: timestamp with time zone ~> GMT

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-07T17:41:46Z

    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