Re: [SQL] avg(interval)
Jeremiah Elliott [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] avg(interval)
Tom Lane wrote: Jeremiah Elliott [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] avg(interval)
Right, the 23 is just less than 1 day, not 23 days.The good news: your query is working!-AaronOn 6/26/06, Joe [EMAIL PROTECTED] wrote:Tom Lane wrote: Jeremiah Elliott [EMAIL PROTECTED] 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
Re: [SQL] avg(interval)
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* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Tom Lane wrote: Jeremiah Elliott [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] avg(interval)
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
