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

Reply via email to