Re: Alter timestamp without timezone to with timezone rewrites rows

On Wed, Jan 13, 2021 at 6:59 AM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)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.