Re: Timezone database changes

=?ISO-8859-1?Q?Magne_M=E6hre?= <Magne(dot)Mahre(at)Sun(dot)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