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.
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 |