CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
Xaedblade
Xaedblade

Reputation: 35

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";

Upvotes: 0

Views: 187

Answers (1)

JGH
JGH

Reputation: 17846

Operations on intervals can lead to these kind of results as operations are done on the years, then on the months, then days etc.

To overcome it, use justify_interval to adjust the exceeding months/days/hours etc

select justify_interval('1 year 35 days 22:17:08.546743'), 
       justify_interval('2 years 9 mons 41 days 13:42:51.453257');
          justify_interval           |            justify_interval
-------------------------------------+-----------------------------------------
 1 year 1 mon 5 days 22:17:08.546743 | 2 years 10 mons 11 days 13:42:51.453257

Upvotes: 1

Related Questions