Re: Converting timestamp to timestamptz without rewriting tables

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