[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] avg(interval)
From:       Joe <dev () freedomcircle ! net>
Date:       2006-06-27 3:20:26
Message-ID: 44A0A3FA.4010404 () freedomcircle ! net
[Download RAW message or body]

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
[prev in list] [next in list] [prev in thread] [next in thread]