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