Re: Timezone database changes

Magne Mæhre <Magne.Mahre@Sun.COM> 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