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


Mon, 09 Aug 2004 16:26:38 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Changing the type of timestamp columns
> 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


Mon, 09 Aug 2004 16:33:38 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Changing the type of timestamp columns
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

Mon, 09 Aug 2004 10:32:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Changing the type of timestamp columns 