From: "Edward W. Rouse" <erouse@comsquared.com>
To: <pgsql-sql@postgresql.org>
References: <032001cce69c$767bc010$63734030$@com>
	<4F32DA3B.9080209@pinpointresearch.com>
In-Reply-To: <4F32DA3B.9080209@pinpointresearch.com>
Subject: Re: time interval math
Date: Wed, 8 Feb 2012 15:49:32 -0500
Message-ID: <032701cce6a3$28c8fca0$7a5af5e0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: Aczmn9OI7fe1rO4pSaenx2/g0aGWBwAAdOZA
Content-Language: en-us

Hehe, thanks, I played around and ended up with this:

round(SUM(extract('epoch' from (time_out - time_in))))

I will have to do the division outside of the query, but that's really a
minor issue. Knowing the total in seconds was the big roadblock. And
converting back is easier (a / 3600 + ":" + a / 60 + ":" + a % 60)

> -----Original Message-----
> From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
> Sent: Wednesday, February 08, 2012 3:26 PM
> To: Edward W. Rouse
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] 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