Thread: avg(interval)

I am getting some really strange query output. I am trying to average an
interval. The problem is that the interval average is sometimes
returning more than 24 hours for the average.

query:
select avg(scan_date -  backflush_date) as time_diff,
substring(pt_article, 1, 5) as clan, sum(qty) as units
from carton_master
join outbound on master_number = master_id
join pt_mstr on product = pt_part  where
scan_date::date  between '2006-06-01'::date and '2006-07-01'::date
and  substring(pt_article, 1, 5) = '*2420'
group by clan

output:
4 days 33:48:13.994333   *2420   25



however if i don't average them here is what i get:
query:
select (scan_date -  backflush_date) as time_diff,
substring(pt_article, 1, 5) as clan
from carton_master
join outbound on master_number = master_id
join pt_mstr on product = pt_part  where
scan_date::date  between '2006-06-01'::date and '2006-07-01'::date
and  substring(pt_article, 1, 5) = '*2420'

output:
"7 days 22:24:50.62311";"*2420"
"9 days 22:21:02.683393";"*2420"
"23:21:35.458459";"*2420"
"4 days 22:47:41.749756";"*2420"
"3 days 06:05:59.456947";"*2420"



which should average to just over nine days -
Assuming I am not missing something huge here. I tend to think its a bug
in avg(interval) postgres function.

I am running postgres 8.1.3 on 32bit suse

Thanks
Jeremiah Elliott

"Jeremiah Elliott" <geek00@gmail.com> writes:
> however if i don't average them here is what i get:
> "7 days 22:24:50.62311";"*2420"
> "9 days 22:21:02.683393";"*2420"
> "23:21:35.458459";"*2420"
> "4 days 22:47:41.749756";"*2420"
> "3 days 06:05:59.456947";"*2420"

> which should average to just over nine days -

Uh ... how do you arrive at that conclusion?  I haven't done the math,
but by eyeball an average of four-something days doesn't look out of
line for those values.
        regards, tom lane

Tom Lane wrote:
> "Jeremiah Elliott" <geek00@gmail.com> writes:
>> however if i don't average them here is what i get:
>> "7 days 22:24:50.62311";"*2420"
>> "9 days 22:21:02.683393";"*2420"
>> "23:21:35.458459";"*2420"
>> "4 days 22:47:41.749756";"*2420"
>> "3 days 06:05:59.456947";"*2420"
> 
>> which should average to just over nine days -
> 
> Uh ... how do you arrive at that conclusion?  I haven't done the math,
> but by eyeball an average of four-something days doesn't look out of
> line for those values.

It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...

Joe

Right, the 23 is just less than 1 day, not 23 days.

The good news: your query is working!

-Aaron

Well, the query is working ok numerically, but should the answer really 
be reported as 4 days and 33 hours?

Erik

Aaron Bono wrote:
> Right, the 23 is just less than 1 day, not 23 days.
>
> The good news: your query is working!
>
> -Aaron
>
> On 6/26/06, *Joe* < dev@freedomcircle.net 
> <mailto:dev@freedomcircle.net>> wrote:
>
>     Tom Lane wrote:
>     > "Jeremiah Elliott" < geek00@gmail.com <mailto:geek00@gmail.com>>
>     writes:
>     >> however if i don't average them here is what i get:
>     >> "7 days 22:24:50.62311";"*2420"
>     >> "9 days 22:21: 02.683393";"*2420"
>     >> "23:21:35.458459";"*2420"
>     >> "4 days 22:47:41.749756";"*2420"
>     >> "3 days 06:05:59.456947";"*2420"
>     >
>     >> which should average to just over nine days -
>     >
>     > Uh ... how do you arrive at that conclusion?  I haven't done the
>     math,
>     > but by eyeball an average of four-something days doesn't look
>     out of
>     > line for those values.
>
>     It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...
>
>     Joe
>


-- 
erik jones <erik@myemma.com>
software development
emma(r)

Erik Jones wrote:
> Well, the query is working ok numerically, but should the answer really 
> be reported as 4 days and 33 hours?

Well, the original poster didn't provide the table schema or PG version, 
but on PG 8.0.3 both with intervals or with differences between 
timestamps, the query appears to work OK:

test=> select * from x;        t
----------------- 7 days 22:24:00 9 days 22:21:00 23:21:00 4 days 22:47:00 3 days 06:05:00
(5 rows)

test=> select avg(t) from x;       avg
----------------- 5 days 09:47:36
(1 row)

test=> select * from x2;          t
--------------------- 2006-06-07 22:24:00 2006-06-09 22:21:00 2006-05-31 23:21:00 2006-06-04 22:47:00 2006-06-03
06:05:00
(5 rows)

test=> select avg(t - '2006-5-31 0:0'::timestamp) from x2;       avg
----------------- 5 days 09:47:36
(1 row)

Joe