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 +
'.5 months'::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 different units 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 very end.

(2) Using absolute time:
Interval math could take a odd shortcut of turning everything
to seconds early in the calculation and 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.