seeming overflow during avg() of intervals without errors/warnings

From: Vick Khera <vivek(at)khera(dot)org>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: seeming overflow during avg() of intervals without errors/warnings
Date: 2013-05-15 14:05:33
Message-ID: CALd+dceOF8guH++KX4F-8P+1nd-Sj6LLT-NPfgnFhSPZ81qBuQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-05-15 14:12:39 Re: FATAL: database "a/system_data" does not exist
Previous Message Adrian Klaver 2013-05-15 13:55:42 Re: FATAL: database "a/system_data" does not exist