Thread: Changing the type of timestamp columns

Hi guys,

Is it safe to update the atttypid of a timestamp column to be a 
timestamptz column?  I wish to do this on a production database, so I 
need to be sure!

Thanks,

Chris

> Is it safe to update the atttypid of a timestamp column to be a 
> timestamptz column?  I wish to do this on a production database, so I 
> need to be sure!

Oh, and what about indexes on them?  Do I just drop them beforehand and 
recreate?

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Is it safe to update the atttypid of a timestamp column to be a 
> timestamptz column?

For sufficiently small values of "safe", sure.

The problem is that unless you live in GMT zone, the interpretation
of values is different: the zero reference for timestamptz is midnight
GMT 2000-01-01, whereas for timestamp it's midnight your local time
2000-01-01.  So if you do the above, all the stored timestamps will
appear to change value by your offset from GMT.

If you're planning to replace all the column entries then it won't
matter, but ...
        regards, tom lane

KL,

> Is it safe to update the atttypid of a timestamp column to be a
> timestamptz column?  I wish to do this on a production database, so I
> need to be sure!

I wouldn't try this without running it against a test database copy first.  
I've already discovered that a backend change to a column data type like your 
describing can disrupt indexes, views, and analyze rows based on the table -- 
the last time I did such, I ended up having to dump and reload the database 
to get everything responding reliably.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

> I wouldn't try this without running it against a test database copy first.  
> I've already discovered that a backend change to a column data type like your 
> describing can disrupt indexes, views, and analyze rows based on the table -- 
> the last time I did such, I ended up having to dump and reload the database 
> to get everything responding reliably.

Yeah, I just remembered table types and pg_depend entries, so I'm not 
really keen to do it any more :/

Chris