PostgreSQL interval returned in days-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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');