Re: timestamp with time zone a la sql99

Dennis,

> It doesn't discuss it. According to the spec a timestamp with time zone is
> a UTC value + a HH:MM offset from GMT. And intervals in the spec is either
> a year-month value or a day-time value. One can only compare year-month
> values with each other and day-time values with each other. So they avoid
> the problem of the how many days is a month by not allowing it.

That's not what Tom and I were talking about. The issue is that the spec
defines Days/Weeks as being an agglomeration of hours and not an atomic
entity like Months/Years are. This leads to some wierd and
calendar-breaking behavior when combined with DST, for example:

template1=> select '2004-10-09 10:00 PDT'::TIMESTAMPTZ + '45 days'::INTERVAL
template1-> ;
?column?
------------------------
2004-11-23 09:00:00-08
(1 row)

Because of the DST shift, you get an hour shift which is most decidely not
anything real human beings would expect from a calendar. The answer is to
try-partition INTERVAL values, as:

Hour/Minute/Second/ms
Day/Week
Month/Year

However, this could be considered to break the spec; certainly Thomas thought
it did. My defense is that the SQL committee made some mistakes, and
interval is a big one.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco