Re: timestamp with time zone ~> GMT

On Mon, Jan 27, 2025 at 9:36 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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