X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 717C39FABE7 for ; Tue, 27 Jun 2006 00:19:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 90924-03 for ; Tue, 27 Jun 2006 00:19:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from vms042pub.verizon.net (vms042pub.verizon.net [206.46.252.42]) by postgresql.org (Postfix) with ESMTP id 01CE59FA2F7 for ; Tue, 27 Jun 2006 00:19:25 -0300 (ADT) Received: from [192.168.7.3] ([71.101.6.8]) by vms042.mailsrvcs.net (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPA id <0J1I0035T18CHYV2@vms042.mailsrvcs.net> for pgsql-sql@postgresql.org; Mon, 26 Jun 2006 22:19:25 -0500 (CDT) Date: Mon, 26 Jun 2006 23:20:26 -0400 From: Joe Subject: Re: avg(interval) In-reply-to: <44A09CB6.8080102@myemma.com> To: Erik Jones Cc: Aaron Bono , Tom Lane , Jeremiah Elliott , pgsql-sql@postgresql.org Message-id: <44A0A3FA.4010404@freedomcircle.net> Organization: Freedom Circle, LLC MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit References: <17eca7f50606261422m77395b89ufb713720defb8b42@mail.gmail.com> <23243.1151357823@sss.pgh.pa.us> <44A05BD4.60900@freedomcircle.net> <44A09CB6.8080102@myemma.com> User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/280 X-Sequence-Number: 25516 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