Re: time interval math

On 02/08/2012 12:01 PM, Edward W. Rouse wrote:
> I'm still working on getting this to work, but the summary is this:
>
> I am getting several (many) intervals of hour, minutes and seconds. I need a
> sum of the absolute value these intervals, similar to the SUM(ABS())
> function for numbers; and I need to divide this sum by an integer (bigint).
> Getting the intervals is no problem, but I can't find built in functions for
> the rest. Currently on 8.3, want to upgrade to 9.x but I can't until this is
> finished.
>
> Do these functions exist, or will I be forced to convert to seconds, do the
> math and then convert back to hour-minute-second format (I am assuming from
> current data that, after the divide, the result should be in the minute:
> second range).

You will have to do some work on your own.

Time and intervals are tricky beasts and depend on the oddities of
daylight saving rules. Even though you are only using
hours/minutes/seconds the interval type also supports days and months. A
day interval is probably 24 hours but could be 23 or 25 if it crosses a
DST boundary. Months have different numbers of days. You have situations
where adding and subtracting a month does not give the original date:

select '2011-03-31'::date - '1 month'::interval + '1 month'::interval;
?column?
---------------------
2011-03-28 00:00:00

There is no abs(interval) function but, if you know that all your
intervals are basic H:M:S and that you won't have any difficulty due to
problems similar to the above you can mimic it with:
...case when myinterval < '0'::interval then '0'::interval - myinterval
else myinterval end...

You are even allowed to sum that and divide it (though I suspect there
are some interesting corner-cases waiting to be discovered):
...sum(case when myinterval < '0'::interval then '0'::interval -
myinterval else myinterval end)/2...

Before you upgrade, be sure to read the release notes and test your
calculations. The way intervals are handled, especially regarding
intervals across DST boundaries, have changed over time. IIRC most of
those changes were pre-8.3 but haven't looked recently.

Cheers,
Steve