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.