Re: timestamp with time zone ~> GMT - Mailing list pgsql-docs
| From | Jonathan Stanley |
|---|---|
| Subject | Re: timestamp with time zone ~> GMT |
| Date | |
| Msg-id | CAM_L50MHqQQ8vU+YwhmV5qTuRHSPsu4KSHiU+v8HObaNOWfFAQ@mail.gmail.com Whole thread Raw |
| In response to | timestamp with time zone ~> GMT (PG Doc comments form <noreply@postgresql.org>) |
| List | pgsql-docs |
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
pgsql-docs by date: