Count of days between dates excluding weekends and holidays-CodePudding

Is there a way to count the days between two dates in PostgreSQL excluding weekends and specific holidays without creating a new table or view? I only have read permission in the database so I can't create a 'Holiday' table or view. I was thinking about using the generate_series function but the holidays do not follow a specific pattern as they vary a lot. Can I create multiple series without following a pattern?

For example: Count the days between 05/01/2022 and 05/31/2022 excluding:

05/01/2022 (Weekend)
05/07/2022 (Weekend)
05/08/2022 (Weekend)
05/14/2022 (Weekend)
05/15/2022 (Weekend)
05/21/2022 (Weekend)
05/22/2022 (Weekend)
05/28/2022 (Weekend)
05/29/2022 (Weekend)

But also excluding:

05/05/2022 (Holiday)
05/11/2022 (Holiday)
05/27/2022 (Holiday)

CodePudding user response：

SELECT
    count(s) FILTER (WHERE (EXTRACT(isodow FROM s::date)
        NOT IN (6, 7))
    AND s::date NOT IN ('2022-05-05', '2022-05-11', '2022-05-27'))
FROM
    generate_series('2022-05-01'::date, '2022-05-31'::date, interval '1 day') g (s);

CodePudding user response：

There are multiple viable solutions, but to avoid specific hard coding in the query (except for the specific range of interest) I would create a calendar table, which specifically indicated business days and holidays. The following gets returns business days that are not also a holiday, for any specified date range. (see demo). Following extend to show each date and the number of days selected.

select daily_date, count(*) over() num_days
  from business_calendar
 where daily_date between <start date> and <end date>
   and is_business_day 
   and not is_holiday
 order by daily_date;