PostgreSQL: How to get last 2 years of data from today's date-CodePudding

I am using PostgreSQL and need to know how to get data from last 2 years starting today, which is current_date. Is my query correct ?

select count(*) from table_name where
creation_date >= date_trunc('year', now()) - interval '2' year and
creation_date < date_trunc('year', now());

CodePudding user response：

Assuming you want a two year span starting from the current date:

select count(*) from table_name where
creation_date >= current_date - interval '2' year and
creation_date < current_date;

Using current_date eliminates the need to truncate now().

CodePudding user response：

What About Extracting only Year from the two parts and make just little comparaison like that;

select count(*) from table_name where
EXTRACT(YEAR FROM  CURRENT_DATE) - EXTRACT(YEAR FROM  creation_date) BETWEEN 0 and 2