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@momjian.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 +