Re: Timestamp vs timestamptz

Florian Weimer <fweimer(at)bfk(dot)de> writes:
> * Agent M.:
>> timestamp with time zone does not record the timezone you inserted it
>> with- it simply stores the GMT version and converts to whatever
>> timezone you like on demand.

> Are you sure? This behavior is not documented, and I can't reproduce
> it with PostgresQL 8.1.4.

Huh? Section 8.5.1.3. Time Stamps says

For timestamp with time zone, the internally stored value is always in
UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
Time, GMT). An input value that has an explicit time zone specified is
converted to UTC using the appropriate offset for that time zone. If no
time zone is stated in the input string, then it is assumed to be in the
time zone indicated by the system's timezone parameter, and is converted
to UTC using the offset for the timezone zone.

and again in section 8.5.3. Time Zones:

All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the timezone
configuration parameter before being displayed to the client.

There's been some talk of modifying timestamptz to store the original
timezone specification along with the actual value, but at the moment
all it is is a seconds-since-the-epoch numeric value.

regards, tom lane