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 <r...@xzilla.net> wrote: > On Mon, Jan 27, 2025 at 9:36 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > Laurenz Albe <laurenz.a...@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 >
