Re: Question about new pg12 feature no-rewrite timestamp to timestamptz conversion

On Mon, May 20, 2019 at 01:13:50PM -0500, Jeremy Finzel wrote:
> I have a question about this (really exciting) feature coming in pg12:
>
> Allow ALTER TABLE .. SET DATA TYPE timestamp/timestamptz to avoid a table
> rewrite when the session time zone is UTC (Noah Misch)
>
> In the UTC time zone, the data types are binary compatible.
>
> We actually want to migrate all of our databases to timestamptz everywhere. 
> But some of them have historically saved data in a *local* time zone with data
> type timestamp.
>
> I assume there is no similarly easy way to do this alter type without a table
> rewrite for a local time zone?  I would assume DST changes would be an issue
> here.
>
> But it would be really nice if we have a table with timestamp data saved @
> America/Chicago time zone, to set the session to 'America/Chicago' and alter
> type to timestamptz, and similarly avoid a table rewrite.  Is this possible or
> feasible?

Well, the timestamptz data type stores the date/time in UTC internally,
and then shifts it to whatever timezone you have set in the client. If
you did the conversion from timestamp _without_ time zone columns, the
new data would take your local time and assume it was stored in UTC,
which I don't think you want. I don't know of a way to make the
adjustment you want without a table rewrite. It is unfortunate that the
SQL standard requires timestamp _without_ time zone to be the default
for 'timestamp'.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +