How to create a SQL date range query where the date range depends on holidays and weekends?

Question

I would like fetch all records in Postgres that match a particular date range. The column 'date' is of type Date.

• Return all rows where date is tomorrow's date
• If today is a Friday, fetch rows that are Saturday, Sunday and Monday
• If Monday is a holiday (ex: July 4), on Friday (7/1), we will fetch transactions for Saturday (7/2), Sunday (7/3), Monday (7/4) and Tuesday (7/5)

There may be multiple holidays in tandem and the logic needs to take that into account. Assume the holidays are all available as an array of dates.

My current solution is to blindly fetch all rows that match tomorrow, and then using code (the app is Ruby on Rails) perform the above logic and look ahead if necessary.

Is there an elegant SQL solution to the above problem?

Answer 1

If you are able to create array of dates to fetch in ruby, as you should be you are doing that already right now to figure out if you need to fetch anything else after tomorrow, you should be able to call something like this:

array_of_dates = [Date.new(2022, 4, 30), Date.new(2022, 5, 1), Date.new(2022, 5, 2)]

MyModel.where(date: array_of_dates).order(:date)

That should be enough. It gets translated to SQL like this:

select * from my_models 
  where date IN ('2022-04-30', '2022-05-01', '2022-05-02') 
  order by date

Order is not necessary.