How to extract year from interval in postgres

Question

I found out that the following line:

select extract(year from '2021-01-01'::timestamp - '2020-01-01');

returns 0.

Even if we go a bit further:

select extract(year from '2021-01-01'::timestamp - '2010-01-01');

The result is still 0.

I understand the rationale behind this. If we run a query to check the interval between consecutive New Years:

select '2021-01-01'::timestamp - '2020-01-01';

We're getting the following result:

0 years 0 mons 366 days 0 hours 0 mins 0.00 secs

1 year wouldn't be precise enough - it can mean 365 or 366 days.

Question: Is there an elegant method to retrieve year count from interval being the difference between two timestamps? Something like the first query, where I would expect result as 1.

clemens clemens · Accepted Answer · 2020-02-06T07:45:52

You should use AGE instead of the difference:

SELECT EXTRACT(YEAR FROM AGE('2021-01-01'::TIMESTAMP, '2010-01-01'::TIMESTAMP));
 date_part 
-----------
        11

See: https://www.postgresql.org/docs/current/functions-datetime.html

Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days

How to extract year from interval in postgres

1 Answers