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