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