PostgreSQL to get the xth business day for the given month-CodePudding

Get xth Business day of a calendar month. For ex. if Nov'21 then 3rd business day is 3rd November, but if Oct'21 3rd business day is 5th Oct. We need to build a query or function to get this dynamically. We need to exclude the weekends (0,6) and any public holidays (from a table with public holidays)..

I believe we dont have a direct calendar function in postgres, may be we can try getting the input as month and integer for (xth business day) we need to get the output as date..

if input : Nov/11 (Month) and 3 (xth Business Day) it will be output: '2021-11-03' as output

CodePudding user response：

create or replace function nth_bizday(y integer, m integer, bizday integer)
returns date language sql as 
$$
select max(d) from
(
 select d 
  from generate_series
  (
   make_date(y, m, 1), 
   make_date(y, m, 1)   interval '1 month - 1 day', 
   interval '1 day'
  ) t(d)
  where extract(isodow from d) < 6
  -- and not exists (select from nb_days where nb_day = d)
  limit bizday
) t;
$$;

select nth_bizday(2021, 11, 11); 
-- 2021-11-15

If you want to skip other non-business days except weekends then the where clause should be extended as @SQLPro suggests, something like this (supposing that you have the non-business days listed in a table, nb_days):

where extract(isodow from d) < 6 
and not exists (select from nb_days where nb_day = d)

CodePudding user response：

Business days are generally specific to organization... You must create a CALENDAR table with date and entries from the begining to the end, with a boolean column that indicates if a day is on or off... Then a view can compute the nth "on" days for every month...