Re: Alter timestamp without timezone to with timezone rewrites rows

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> 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.

The physical representation of the data is identical, yes, but the
*interpretation* is not. In modern PG, timestamptz is int64 microseconds
counted from 2000-01-01 00:00:00 UTC, while timestamp is int64 microseconds
counted from 2000-01-01 00:00:00 local time (whatever you think local time
is). Also, while the display (not storage) of timestamptz accounts for
local daylight-savings rules, display of timestamp values does not.
Thus, the offset between the stored values of timestamptz and timestamp
for the "same" date/time will vary over time. Where I live, there's
a five-hour offset right now, but for much of the year it's a four-hour
offset.

So a non-rewriting conversion would only be possible if local time is
identical to UTC; which is true for few enough people that nobody has
bothered with attempting the optimization. (From memory, the existing
method for deciding whether non-rewriting conversion is possible could not
cope with such an environment-dependent rule anyway, so some significant
trouble would be involved to figure out how to do it.)

regards, tom lane