pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedavg(interval)
6+ messages / 5 participants
[nested] [flat]
* avg(interval)
@ 2006-06-26 21:22 Jeremiah Elliott <geek00@gmail.com>
2006-06-26 21:37 ` Re: avg(interval) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jeremiah Elliott @ 2006-06-26 21:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avg(interval)
2006-06-26 21:22 avg(interval) Jeremiah Elliott <geek00@gmail.com>
@ 2006-06-26 21:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 22:12 ` Re: avg(interval) Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-06-26 21:37 UTC (permalink / raw)
To: Jeremiah Elliott <geek00@gmail.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avg(interval)
2006-06-26 21:22 avg(interval) Jeremiah Elliott <geek00@gmail.com>
2006-06-26 21:37 ` Re: avg(interval) Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-26 22:12 ` Joe <dev@freedomcircle.net>
2006-06-27 00:53 ` Re: avg(interval) Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 6+ messages in thread
From: Joe @ 2006-06-26 22:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeremiah Elliott <geek00@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avg(interval)
2006-06-26 21:22 avg(interval) Jeremiah Elliott <geek00@gmail.com>
2006-06-26 21:37 ` Re: avg(interval) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 22:12 ` Re: avg(interval) Joe <dev@freedomcircle.net>
@ 2006-06-27 00:53 ` Aaron Bono <postgresql@aranya.com>
2006-06-27 02:49 ` Re: avg(interval) Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 6+ messages in thread
From: Aaron Bono @ 2006-06-27 00:53 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Jeremiah Elliott <geek00@gmail.com>; pgsql-sql
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> wrote:
>
> 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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avg(interval)
2006-06-26 21:22 avg(interval) Jeremiah Elliott <geek00@gmail.com>
2006-06-26 21:37 ` Re: avg(interval) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 22:12 ` Re: avg(interval) Joe <dev@freedomcircle.net>
2006-06-27 00:53 ` Re: avg(interval) Aaron Bono <postgresql@aranya.com>
@ 2006-06-27 02:49 ` Erik Jones <erik@myemma.com>
2006-06-27 03:20 ` Re: avg(interval) Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 6+ messages in thread
From: Erik Jones @ 2006-06-27 02:49 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Joe <dev@freedomcircle.net>; Tom Lane <tgl@sss.pgh.pa.us>; Jeremiah Elliott <geek00@gmail.com>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avg(interval)
2006-06-26 21:22 avg(interval) Jeremiah Elliott <geek00@gmail.com>
2006-06-26 21:37 ` Re: avg(interval) Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 22:12 ` Re: avg(interval) Joe <dev@freedomcircle.net>
2006-06-27 00:53 ` Re: avg(interval) Aaron Bono <postgresql@aranya.com>
2006-06-27 02:49 ` Re: avg(interval) Erik Jones <erik@myemma.com>
@ 2006-06-27 03:20 ` Joe <dev@freedomcircle.net>
0 siblings, 0 replies; 6+ messages in thread
From: Joe @ 2006-06-27 03:20 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Aaron Bono <postgresql@aranya.com>; Tom Lane <tgl@sss.pgh.pa.us>; Jeremiah Elliott <geek00@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-06-27 03:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-26 21:22 avg(interval) Jeremiah Elliott <geek00@gmail.com>
2006-06-26 21:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 22:12 ` Joe <dev@freedomcircle.net>
2006-06-27 00:53 ` Aaron Bono <postgresql@aranya.com>
2006-06-27 02:49 ` Erik Jones <erik@myemma.com>
2006-06-27 03:20 ` Joe <dev@freedomcircle.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox