Trevor Talbot wrote:
> Actually, I'm used to knowing how PostgreSQL does it, but looking at
> things again I remember some confusion I had when first encountering
> the timestamp types. I don't know what the SQL Standard says; is the
> implication that "timestamp with time zone" actually stores the
> literal time and the zone it is associated with? (Would make more
> sense, given the name.)
SQL itself doesn't say anything how the data element should be stored,
only how it should be operated upon. It do, however,say that a
datetime/time WITH TIME ZONE represents the time in UTC (SQL 2003,
§4.3). All operations on the element are defined as if it's an instance
in time (in UTC).
Interestingly, if you cast a TIMESTAMP WITH TIME ZONE to a character
value, it should be converted with the _original_ time zone value (SQL
2003, §5.8) _unless_ you specify "AT LOCAL".
In the database, it makes sense to store the time instance in UTC (for
efficiency) and only apply the offset for presentation.
--Magne