Re: Alter timestamp without timezone to with timezone rewrites rows - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: Alter timestamp without timezone to with timezone rewrites rows
Date
Msg-id CAKFQuwZdtbFaAfo33VtWYhcmDN==eu4JS=Ed_GGGYzJNMO=_cA@mail.gmail.com
Whole thread Raw
In response to Re: Alter timestamp without timezone to with timezone rewrites rows  (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>)
Responses Re: Alter timestamp without timezone to with timezone rewrites rows  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On Wed, Jan 13, 2021 at 6:59 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
+01 indicates that there's timezone information added to the data, so
the rows aren't identical. Here's some more SQL run on my laptop which
shows that

This is indeed true but examples that use the textual representation of the data don't support the claim.  Both types effectively store a point-in-time in UTC, the "with timezone" just does so explicitly - but the behavior for conversions from "without timezone" treat the stored time as being UTC as well.  It would be possible to simply store a timezone-less timestamp in both cases and assume UTC (or not) when displaying the value based upon whether the datatype of the value is determined to be "with timezone" or not (the former also undergoing rotation based upon the runtime value of the timezone setting).  The fact that pg_attribute is required to interpret the data suggests that the stored data doesn't care about its named datatype and that runtime interpretation of the value based upon datatype would be possible.

To be clear, I don't envision the current status changing - we were able to avoid a rewrite with varchar(n) -> text because the stored data was indeed identical.  Reading the documentation it does say, though maybe not as explicitly as it could, that the physical storage of a timestamptz includes an explicit UTC data component ("the internally stored value is always in UTC") while the storage of a timestamp does not (i.e., there is no such verbiage).

David J.



pgsql-hackers by date:

Previous
From: Sehrope Sarkuni
Date:
Subject: Re: Moving other hex functions to /common
Next
From: James Coleman
Date:
Subject: Re: [DOC] Document concurrent index builds waiting on each other