Working days PostgreSQL function

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 ?!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s