If I inherit a Postgresql database filled with TIMESTAMP type, and feel the need to convert them all to TIMESTAMPTZ type, is this a simple process requiring only:

ALTER TABLE mytable ALTER COLUMN the_time_stamp TYPE TIMESTAMPTZ;

I have such a database, and all the timestamps within it, created in a timezone different to mine (and the server's) behave such that they seem to be stored internally as TIMESTAMPTZ anyway. That is, I have two clients (one write, one read, neither of which is in UTC zone), and when I read, the timestamps written in zone1 are correct when read in zone2, implying (to me) they have been converted and stored in UTC format between the write and read. It makes me wonder if:

• Are all timestamps stored in identical format in the database, and the only differences occur in the way input/output is handled, according to the timezone(s) of the connected client(s)?

This seems like such a clear and simple thing to state if it were true, but I cannot find it stated clearly and/or simply...

CodePudding user response：

Internally, both timestamp and timestamp with time zone are stored in the same fashion: an 8-byte integer that is the offset from 2000-01-01 00:00:00 in microseconds.

The difference is the semantics: while timestamp with time zone is stored as offset from midnight 2000-01-01 in UTC, no time zone conversion is made for timestamp.

That means that the table has to be rewritten if you change the data type, because the point of reference changes. If the timezone parameter is set to UTC, the values won't change, but there is no optimization in PostgreSQL that avoids the table rewrite in this case.