Re: Broken(?) 'interval' problems. [Was: ISO 8601 "Time Intervals"]

Bruno wrote:
> 
> Can you document which part of a mixed interval (with both months and
> seconds parts) gets added first to a timestamp? I haven't ever run
> across anything which says which gets done first.
> 

In the existing code, the sql spec, or the proposed implementation?

In the existing code, I think everything with "+" gets done
in in the same order (left-to-right?), regardless of if the
fields are timestamps or intervals.

This leads to cool crazy behavior like getting different
answers for this:
 logs=# select '.5 months'::interval +                '.5 months'::interval +                '2003-01-01'::timestamp;
   ?column? ---------------------  2003-01-01 00:00:00 (1 row)  logs=# select '2003-01-01'::timestamp +
'.5months'::interval +               '.5 months'::interval;         ?column? ------------------------   2003-01-31
00:00:00-08(1 row)
 

With addition not being commutative, all sorts of pain can result.
The thing I'm proposing, is to define a form of time-math
that is as consistant as possible.

There are at least two reasonable ways of doing this -- using 
calendar time, or using absolute time.  

ISO 8601 makes such distinctions between "day" which it
defines as 24 hours, and "calendar day" which it defines 
as 24 hours +/- leap minutes and seconds.

The way this would work, we could:
 (1) Using calendar time:     When doing math on 'intervals' and 'timestamps', we would keep      the fundementally
differentunits separate until the end.     This means keeping separate track of        years & months  in units of
months       weeks & days    in units of days        hours and less  in units of seconds     through out the
calculation.    This means you could have an intervals of '.5 months' without     it converting to 15 days until the
veryend.
 
 (2) Using absolute time:     Interval math could take a odd shortcut of turning everything     to seconds early in the
calculationand converting back at     the end.
 

I actually think each of the two are useful for different applications;
so I'm really tempted to create a GUC parameter date_math = 'absolute' or 'calendar'
to select between the two.