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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
