I have this basic PostgreSQL query:
SELECT AGE('2021-01-21', '1942-11-20');
Which returns an interval in days:
output: 28531 days, 0:00:00
I am using PostgreSQL version 14, according to the docs, AGE() should return a symbolic result in years, months and days instead of just days.
Does anyone know why this interval is returned in days instead of in years, months, days?
Function age returns type interval which you can format as you need using to_char. Here is an illustration. I would strongly suggest that you control the presentation explicitly rather than rely on defaults.
SELECT to_char(age('2021-01-21','1942-11-20'), 'yy "years", mm "months and" dd "days"');
SELECT to_char(age('2021-01-21','1942-11-20'), 'yy-mm-dd');
Results:
78 years, 02 months and 01 days
78-02-01
age out of the box works correctly: SELECT AGE('2021-01-21', '1942-11-20'); 78 years 2 mons 1 day, so the above is unnecessary. The issue is something else, most likely a non-standard(user created) version of age() floating around.
age() is not returning the value it should. You are answering what can I do with the return value of age to make it be a different format.SET intervalstyle = 'sql_standard' before SELECT AGE and see the result.intervalstyle settings. That still does not relate to the OP's question. With none of the settings will you get 28531 days, 0:00:00 using age(). There is an issue with the age function itself, most likely because there is a non-standard version of it in the database. That is the issue needing an answer.What you're saying is correct it should display the years, months and days.
Maybe you could use this statement where you specify the format you'd like to recieve to get the desired ouput:
SELECT AGE(timestamp '2021-01-21', timestamp '1942-11-20', 'YYYY-MM-DD');
age()in your database. Inpsqldo\df ageand add the results as update to your question.SELECT '2021-01-21'::date - '1942-11-20'::date; 28552so the result for your output is incorrect.