From: Jonathan Stanley <bristleconeweb@gmail.com>
To: Robert Treat <rob@xzilla.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: timestamp with time zone ~> GMT
Date: Mon, 3 Feb 2025 11:50:57 -0500
Message-ID: <CAM_L50MHqQQ8vU+YwhmV5qTuRHSPsu4KSHiU+v8HObaNOWfFAQ@mail.gmail.com> (raw)
In-Reply-To: <CABV9wwOfPK_NtzfPf4Kh4SvkxUHZpP6YprquQwPTndEnB7JSwA@mail.gmail.com>
References: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
<7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at>
<391380.1737988609@sss.pgh.pa.us>
<CABV9wwOfPK_NtzfPf4Kh4SvkxUHZpP6YprquQwPTndEnB7JSwA@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bristleconeweb@gmail.com, rob@xzilla.net, tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, pgsql-docs@lists.postgresql.org
Subject: Re: timestamp with time zone ~> GMT
In-Reply-To: <CAM_L50MHqQQ8vU+YwhmV5qTuRHSPsu4KSHiU+v8HObaNOWfFAQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox