Looking for a way to have a handy manner to calculate the number of working days between two dates ?! Here is a howto.
You’ll adjust the following according to your needs, besides feeding reference tables with worldwide non-working days plus working day weeks might be tricky if not costly.
The following examples use the ISO 3166-1 country code standard for two chars country codes.
Say you have two reference tables :
- week working days (they might differ from one country to another)
- public holidays
Let’s call the first one week_noworking_days :
CREATE TABLE "week_noworking_days" ( "country" character(2) NOT NULL, "day" "int" NOT NULL, );
Feed that table with days of the week (dow) that are no-working days per country : most countries have saturdays and sundays off (dow 0 and 6) but there are exceptions.
Have a second reference table holding bank holidays :
CREATE TABLE "bank_holidays" ( "country" character(2) NOT NULL, "day" "date" NOT NULL, "label" "text" );
Feed it easily with google calendars, e.g. https://www.google.com/calendar/ical/en.usa%23holiday%40group.v.calendar.google.com/public/basic.ics
Might not exaclty suit your needs though, there are paying solutions around otherwise.
Once you have the precedings set up the following PostgreSQL stored procedure will return the number of working days between two dates taking a list of country as parameter :
CREATE OR REPLACE FUNCTION working_days_between (IN _from date, IN _to date, IN _countries text[] DEFAULT ARRAY['US'::TEXT]) RETURNS int8 AS $BODY$ SELECT CASE WHEN _from <= _to THEN count(*) ELSE - count(*) END FROM (SELECT CASE WHEN _from <= _to THEN _from+x ELSE _to+x END AS dday FROM generate_series(1, abs(_to - _from)) x EXCEPT SELECT day::date FROM bank_holidays WHERE country = ANY (_countries)) AS days WHERE extract('dow' FROM dday) NOT IN (SELECT day FROM week_noworking_days where country = ANY(_countries)); $BODY$ LANGUAGE 'sql' STABLE;
Useful ?!