I'm attempting to compute an average age of some records based on a create_date field.
The simple query of
select avg(now() - user_created) from user_list where owner_id = 1 and user_status = 'active';
returns nonsense: -487 days -06:46:14.61501
The minimum user_created value is '2006-06-05 09:31:13-04', and the clock is correct for the current time. The user_created field is type "timestamp(0) with timezone, not null"
Couple of other interesting points:
select min(now() - user_created) from user_list where owner_id=1;
min
-----------------
00:58:10.419604
select max(now() - user_created) from user_list where owner_id=1;
max
---------------------------
2535 days 23:50:57.208109
So basically, it seems like however avg() on intervals is being computed it is overflowing somewhere without warning. I cannot figure out how to issue appropriate casts to make it not do so. In this case there are several million records that are involved.
I did find an alternate query that does not return nonsense:
select avg(age(now(),user_created)) from user_list where owner_id = 1 and user_status = 'active';
which says: 2 years 3 mons 42 days 19:19:15.100571
PostgreSQL 9.2.4 on FreeBSD 9.1 64-bit.
Advice? The original query seems to work for other subsets of the user_list, so to have it fail in such a crazy manor without any warnings is troublesome.