7

I would like to know the average age of my users and did the following to do that:

# SELECT avg(age(birthday)) FROM "user";
                   avg
------------------------------------------
 33 years 10 mons 32 days 08:33:10.577946

What does the number of days mean? How can it be over 31 days?

I have 3746 records and I'm on the UTC time zone.

PS: I'm using Postgres 9.5.3

  • Are you in a timezone that's more than 9 hours out? It's the only thing I can think of that's made it a (buggy) presentation issue. – Philᵀᴹ Jul 29 '16 at 13:31
  • 2
    You can use justify_days() (or justify_interval) to "normalize" the interval – a_horse_with_no_name Jul 30 '16 at 20:54
  • 1
    Does SELECT age(avg(birthday)) FROM "user"; give the same result? – ypercubeᵀᴹ Jul 30 '16 at 22:59
  • @ypercubeᵀᴹ, I tried that before, but avg(birthday) gives "No function matches the given name and argument types." – n1r3 Aug 2 '16 at 9:12
  • @a_horse_with_no_name this does the trick ! Thank you. Any clue why this isn't the default behavior thought? – n1r3 Aug 2 '16 at 9:13
1

age() returns intervals. In SQL-92 "an Interval is an unanchored directional duration of the time line" [1]. They have two types due to different month lengths in Gregorian calendar (year to month and day to second). Oracle gives you an error message, if you try to do what you did. Intervals can be positive and negative. They need to be anchored to have a precise meaning, like date + interval = date2. Taking averages of these values is ill-defined in general and trying it for age-values might still give you unexpected results (as it does).

So what is the meaning of a 3 month 32 days in Postgres? Well only the code can tell (or the one who has written it) for sure. I guess it means "advance one month, then 32 days". One can't convert days to months or vice versa.

How can it happen to be there? Average is sensitive to outliers, so if some rather large value is in there for days, it will have an influence. How are null values handled? Did some users specify dates in the future or gave unreasonable values? Is there some implicit conversion going on? Have Postgres developers created a special average function for intervals?

For your problem at hand I recommend using (sorry if this doesn't work, I don't have a database at hand):

select avg(extract(epoch from now()) - extract(epoch from birthday)) from "user";

This works for users of reasonable age, but if you had users from the 1700th,the answer would also depend on location, as the new calendar was adopted later in some countries.

Read the following book to get to know other quirks with dates.

[1] "Developing time-oriented database applications in SQL", Richard T Snodgrass, Morgan-Kaufmann, 1999. See his homepage, Pages 30-32.

| improve this answer | |
  • extract(epoch FROM age(birthdate)); sounds better to me. – Evan Carroll Feb 14 '17 at 16:36
  • If you use extract(epoch from '1 month')' the value is undefined. A one month delta can be 28, 29, 30 or 31 days. The semantics of age(t)` being now()-t allow a correct conversion, but any other reference value might be wrong. Taking the delta at the level of unix time stamps allows correct generalization (ignoring leap seconds of course) and is therefore a correct way to be used in production code. – Grimaldi Feb 14 '17 at 16:55
0

The Function AGE()returns a interval[1] value.

If you want compute only the years you must extract it com the age function, eg:

SELECT AVG(EXTRACT(year FROM AGE(birthday))) FROM user;

Please, take a look at the documentation[2] for more details.

References:

  1. https://www.postgresql.org/docs/current/static/datatype-datetime.html
  2. https://www.postgresql.org/docs/9.5/static/functions-datetime.html
| improve this answer | |
  • Averaging the number of years result in a loss of precision, no? – n1r3 Aug 11 '16 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.