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
