Postgres Pro
Downloads
Home   >   mailing lists

Thread: Converting timestamp to timestamptz without rewriting tables

Converting timestamp to timestamptz without rewriting tables

From
Tom Dunstan
Date:
Hi all

We have generally been using timestamps without timezones in our system. As both our app servers and db server were set to UTC it so far hasn't been an issue. However, that may not always be the case, so we want to tighten things up a bit. We are also needing to do things like get the particular date that a timestamp in the db refers to, and thus need to be explicit about which timezone everything is in.

All of the timestamps in our system represent an instant in time, not a clock date/time, so timestamp with time zone is more appropriate. All of the data that is currently on disk in timestamp columns was inserted in a db session in UTC, and represents that timestamp in UTC.

We'd really like to convert all of our timestamps to timestamp with time zones. We'd prefer not to have to rewrite all of our tables as a result though.

My understanding from reading the docs is that a timestamp in UTC will have the same on-disk representation as a timestamptz. This suggested simply tweaking the system catalogs in some way. It looks like the preferred way to do it would be creating a cast with WITHOUT FUNCTION between the two and then doing an ALTER TABLE for each table affected, but a cast between the two already exists.

I ended up trying out just tweaking the system catalogs directly, changing the type associated with the table columns and the opclasses associated with the related indexes.

UPDATE pg_attribute
  SET atttypid = 'timestamp with time zone'::regtype
FROM pg_class
WHERE attrelid = pg_class.oid
      AND relnamespace = current_schema()::regnamespace
      AND atttypid = 'timestamp'::regtype
      AND relname NOT IN ('some', 'excluded_tables');

-- These (3127, 3128) are hardcoded in pg sourcecode
UPDATE pg_index
SET indclass = array_to_string(array_replace(indclass::oid[], 3128::oid, 3127::oid), ' ')::oidvector
FROM pg_class
WHERE indrelid = pg_class.oid
AND relnamespace = current_schema()::regnamespace
AND indclass::oid[] @> ARRAY[3128::oid];

If we do this for real we can shut out other clients while it happens so we don't need to worry about other backends having cached plans with the wrong type etc.

It.. seems to work. Data is returned as expected and queries using the indexes seem to work. I dropped and recreated functions and views that had any reference to the affected columns.

Questions:

1. Is there a safer way to change these types without this hackery?
2. If not, is there anything else that I need to adjust?
3. Is there anything that could go wrong?

Many thanks
Tom

Re: Converting timestamp to timestamptz without rewriting tables

From
Thomas Kellerer
Date:
Tom Dunstan schrieb am 18.12.2017 um 09:08:
> We have generally been using timestamps without timezones in our
> system. As both our app servers and db server were set to UTC it so
> far hasn't been an issue. However, that may not always be the case,
> so we want to tighten things up a bit. We are also needing to do
> things like get the particular date that a timestamp in the db refers
> to, and thus need to be explicit about which timezone everything is
> in.
> 
> All of the timestamps in our system represent an instant in time, not
> a clock date/time, so timestamp with time zone is more appropriate.
> All of the data that is currently on disk in timestamp columns was
> inserted in a db session in UTC, and represents that timestamp in
> UTC.


Are you aware of the fact that "timestamp with time zone" does NOT actually store the time zone? 

A timestamptz stores everything as UTC and the value is converted to the session time zone upon retrieval. 

Thomas

Re: Converting timestamp to timestamptz without rewriting tables

From
Tom Dunstan
Date:


On 18 December 2017 at 18:43, Thomas Kellerer <spam_eater@gmx.net> wrote:
> All of the timestamps in our system represent an instant in time, not
> a clock date/time, so timestamp with time zone is more appropriate.
> All of the data that is currently on disk in timestamp columns was
> inserted in a db session in UTC, and represents that timestamp in
> UTC.


Are you aware of the fact that "timestamp with time zone" does NOT actually store the time zone?

Yes.
 
A timestamptz stores everything as UTC and the value is converted to the session time zone upon retrieval.

Which is exactly what we want.

Re: Converting timestamp to timestamptz without rewriting tables

From
Corey Taylor
Date:
On Mon, Dec 18, 2017 at 2:18 AM, Tom Dunstan <pgsql@tomd.cc> wrote:
>
>>
>> A timestamptz stores everything as UTC and the value is converted to the session time zone upon retrieval.
>
>
> Which is exactly what we want.

If a random person can interject here, I believe what Tom Dunstan is asking about here is the cheapest way to do a raw conversion of *all* columns in *all* tables with the underlying assumption that the data does not need to be touched at all (as the data representation is the same between timestamp and timestamptz).

Hence he is showing manipulating the pg_attribute and pg_table values.