agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: bristleconeweb@gmail.com
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: timestamp with time zone ~> GMT
Date: Mon, 27 Jan 2025 09:36:49 -0500
Message-ID: <391380.1737988609@sss.pgh.pa.us> (raw)
In-Reply-To: <7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at>
References: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org>
<7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at>
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
view thread (12+ messages) latest in thread
Message-ID: <391380.1737988609@sss.pgh.pa.us>
Permalink: ../391380.1737988609@sss.pgh.pa.us/
Also on: postgresql.org/message-id/391380.1737988609@sss.pgh.pa.us
reply
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: tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, bristleconeweb@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: timestamp with time zone ~> GMT
In-Reply-To: <391380.1737988609@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox