Thread: 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@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 +