=?ISO-8859-1?Q?Magne_M=E6hre?= <[EMAIL PROTECTED]> writes: > I would suggest that the WITH TIMEZONE elements are converted to UTC when > inserted into the database. Since all operations on it are based on > its UTC form, it's most efficient ( I believe) if the data is stored that > way. To be compliant, an offset (hours and minutes) to the time zone > that was used when storing the time should be stored as well.
Well, the question is what would we *do* with the latter? If we have that override the TimeZone zone for output, we will break a lot of things. There's also the question of what to put into a computed timestamp value. Consider regression=# select timestamptz '2007/10/01 00:00 EDT'; timestamptz ------------------------ 2007-10-01 00:00:00-04 (1 row) regression=# select timestamptz '2007/10/01 00:00 EDT' + interval '3 months'; ?column? ------------------------ 2008-01-01 00:00:00-05 (1 row) I think the latter behavior (that you get midnight EST not EDT) is generally agreed to be desirable, but I don't see any very principled way to achieve it if UTC offsets (as opposed to timezones) are considered "sticky". The proposal to store a zone identifier (*not* a raw UTC offset) is somewhat more defensible but it's still got issues. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match