'Issues with calculating average age but getting incorrect results in postgres
I am getting the average age of an account. to do so I have employed the following code.
select
m."storeId", s."locationName",
COUNT(m."membershipEnded" is not null) as "Cancelations",
AVG(AGE(m."membershipEnded"::date, m."memberSince"::date)) as "Membership Duration in days"
FROM "members" m
INNER JOIN "stores" s on s."storeId" = m."storeId"
where (m."membershipEnded" is not null
and m."membershipEnded"::date > m."memberSince"::date)
and "memberSince" is not null
and f."countryCode" in ('US','CA')
and s."storeStatus" ='Active'
group by 1,2
order by "storeId";
The results that I get include invalid days, so one example is: "1 year 35 days 22:17:08.546743" or another one with "2 years 9 mons 41 days 13:42:51.453257".
I've searched everywhere but have found the reason for this miscalculation of the days.
Any and all help will be appreciated.
I will also note that I have just subtracted the two to get the days difference, but I prefer the results with the years, months, and days broken out like it is with age.
Postgresql version is 10.9
***** EDIT - ANSWER the updated code with the answer from below:
justify_interval() worked like a charm
select
m."storeId", s."locationName",
COUNT(m."membershipEnded" is not null) as "Cancelations",
justify_interval(AVG(AGE(m."membershipEnded"::date, m."memberSince"::date))) as "Membership Duration in days"
FROM "members" m
INNER JOIN "stores" s on s."storeId" = m."storeId"
where (m."membershipEnded" is not null
and m."membershipEnded"::date > m."memberSince"::date)
and "memberSince" is not null
and s."countryCode" in ('US','CA')
and s."storeStatus" ='Active'
group by 1,2
order by "storeId";
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
