=?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';
 2007-10-01 00:00:00-04
(1 row)

regression=# select timestamptz '2007/10/01 00:00 EDT' + interval '3 months';
 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

Reply via email to