How to Select Between Two Specific Date Ranges-CodePudding

Say I want a specific interval like (mm/dd/yyyy-1 - 1/1/yyyy-1)

How could I query this in postgres?

So far I've worked out something along the lines of:

SELECT * 
FROM my_table 
WHERE date BETWEEN (now()::DATE - INTERVAL '1 year') - ‘1/1/?’ AND (now() - INTERVAL '1 year') - ‘1/1/?’

I'm not quite sure how to get the previous year.

Am I on the right track? Sorry, I'm new to this. Thanks!

CodePudding user response：

You can use date_trunc() to get the beginning of the year:

select *
from my_table
where date >= date_trunc('year', current_date - interval '1 year')
  and date  < date_trunc('year', current_date)

date_trunc('year', current_date) returns the first day of this year. As I have used < as the upper bound, this will include all dates until the December, 31st in the previous year.

If you do want to use BETWEEN (which will only work properly if date is defined with the data type date) then you can use:

select *
from my_table
where date between date_trunc('year', current_date - interval '1 year')::date
               and date_trunc('year', current_date)::date - 1

Note that the upper limit was cast to a date and then I subtracted 1 day to land on December 31. This is necessary because between includes both values.

CodePudding user response：

If you want, you can use something like this:

select * from table 
 where data between to_date(concat('01/01/', EXTRACT (YEAR FROM now()) - 1), 'mm/dd/yyyy') and (now()::DATE - interval '1 year')

But the more used is date_trunc:

select * from table
where date >= date_trunc('year', now()::DATE - interval '1 year')
  and date  < date_trunc('year', now()::DATE)

When you use date_trunc('year', now()::DATE) the day will be 01, month 01, and year will be the this year.