To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: time interval math
Date: 9 Feb 2012 11:36:57 GMT
Organization: Dis (not Dat) Organisation
Lines: 29
Message-ID: <jh0b4p$ruj$2@reversiblemaps.ath.cx>
References: <032001cce69c$767bc010$63734030$@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

On 2012-02-08, Edward W. Rouse <erouse@comsquared.com> 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.

the operation abs() is meaninless on the type interval
eg: what is abs( '1 month - 32 days + 24 hours'::interval )
 
howevwer since all your intervals are in seconds (postgres pretends that all
hours are 3600 seconds long) converting to seconds is probably the
best way to go.

> 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).

Yeah, you need to do that, it's not hard,

select ( sum(abs(extract('epoch' from YOUR_INTERVAL ))) / SOMETHING ) * '1s'::interval
from ...

-- 
⚂⚃ 100% natural