1

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?

Ewald
  • 11
  • 1
  • It's duplicated for sure – oguzhan00 Dec 15 '22 at 10:15
  • Hi @oguzhan00, your comment does not really help me as you don't link me the question from which you think this is a duplicate... – Ewald Dec 15 '22 at 10:59
  • Best guess is that there is another version of `age()` in your database. In `psql` do `\df age` and add the results as update to your question. – Adrian Klaver Dec 15 '22 at 16:09
  • Another thought, in what client are you running the query? Also `SELECT '2021-01-21'::date - '1942-11-20'::date; 28552` so the result for your output is incorrect. – Adrian Klaver Dec 15 '22 at 17:04
  • @AdrianKlaver thanks for your comments! I'm using pyodbc v4.0.32 driver to connect to the database and run queries using Python. – Ewald Dec 16 '22 at 15:24
  • 1) You have not addressed the most import part of my comments, is there another version of `age()` in the database. Connect with `psql` and do `\df age`. 2) Per previous request **add information as update to question** not in the comments. – Adrian Klaver Dec 16 '22 at 15:45

2 Answers2

1

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
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • `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. – Adrian Klaver Dec 15 '22 at 16:11
  • @AdrianKlaver Yet content and presentation are different concerns that is good to keep separate. – Stefanov.sm Dec 15 '22 at 16:23
  • But your answer does not address the OP's question which is `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. – Adrian Klaver Dec 15 '22 at 16:27
  • @AdrianKlaver It does as I am only saying that implicit "symbolic" format shall not be relied upon. Run `SET intervalstyle = 'sql_standard'` before `SELECT AGE` and see the result. – Stefanov.sm Dec 15 '22 at 16:54
  • I am familiar with the `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. – Adrian Klaver Dec 15 '22 at 17:02
  • Lastly if you plug in the OP's value into your answer: `select to_char('28531 days'::interval ,'yy "years", mm "months and" dd "days"');` you get `00 years, 00 months and 28531 days`. To get close you would need: `select to_char(justify_days('28531 days'::interval) ,'yy "years", mm "months and" dd "days"'); 79 years, 03 months and 01 days`. Which is actually different from what: `SELECT AGE('2021-01-21', '1942-11-20')` returns `78 years 2 mons 1 day`. Again pointing out there is a problem with `age` in the OP's database. – Adrian Klaver Dec 15 '22 at 17:49
0

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');
Desmanado
  • 152
  • 9