Business Days / Working Days SQL for Postgres

by

We use Postgres 8.4 as our database and I’m really impressed with how it performs and the amount of features it has.

Recently we’ve needed some functions for working with dates and business days, so I looked for some help on this.

I found a good post here: http://archives.postgresql.org/pgsql-sql/2009-11/msg00038.php

In the process, I made a few changes, including the use of Postgres 8.4 ‘with’ syntax, which I find really useful in making things understandable. Obviously, this needs Postgres 8.4 to work.

Here are the results, in case anyone out there is looking for similar functions (any comments / suggestions / improvements welcome):

Holiday Table
First, create a table containing all the holidays that aren’t working days:

CREATE TABLE holidays (
   id serial,
   description character varying (50) not null,
   holiday date not NULL
)
WITHOUT OIDS
TABLESPACE pg_default;

insert into holidays (description, holiday) values ('Christmas Day', '2009-12-25');
insert into holidays (description, holiday) values ('Boxing Day', '2009-12-28');
insert into holidays (description, holiday) values ('New Years Day', '2010-01-01');
insert into holidays (description, holiday) values ('Good Friday', '2010-04-02');
insert into holidays (description, holiday) values ('Easter Monday', '2010-04-05');

Add Working Days to a Date
Now, create a function to add working days to a given date:
NOTE: If you input a non-working day and add 0 days to it, you will get no result!

CREATE OR REPLACE FUNCTION addbusinessdays(date, integer)
  RETURNS date AS
$BODY$
with alldates as (
	SELECT i,
	-- Check for negatives
	$1 + (i * case when $2 < 0 then -1 else 1 end) AS date
	-- NOTE we add 5 and x 2, to make sure the sequence has enough in it to cope with weekends/hols that get omitted later
	FROM generate_series(0,(abs($2) + 5)*2) i
),
days as (
	select i, date, extract('dow' from date) as dow
	from alldates
),
businessdays as (
	select i, date, d.dow from days d
	left join holidays h on d.date=h.holiday
	where h.holiday is null
	and d.dow between 1 and 5
	order by i
)
--select count(*) from businessdays where date between '2010-04-01' and '2010-04-07';
-- now to add biz days to a date
select date from businessdays where
        case when $2 > 0 then date >=$1 when $2 < 0 then date <=$1 else date =$1 end
	limit 1
	offset abs($2)
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;
ALTER FUNCTION addbusinessdays(date, integer) OWNER TO postgres;

select * from addbusinessdays('2009-12-24', 1)

Finally to get the number of working days between two dates:

CREATE OR REPLACE FUNCTION countbusinessdays(date, date)
  RETURNS bigint AS
$BODY$
with alldates as (
	SELECT i, $1 + i AS date, extract ('dow'  from $1::date + i) AS dow
	FROM generate_series(0,$2-$1) i
),
businessdays as (
	select date, dow from alldates dt
	left join holidays h on dt.date=h.holiday
	where h.holiday is null
	and dow between 1 and 5
	order by date
)
select greatest(0,count(*)-1) from businessdays where date between $1 and $2;

$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;
ALTER FUNCTION countbusinessdays(date, date) OWNER TO postgres;

select * from countbusinessdays('2009-12-24','2009-12-29');

That’s it! Hope it’s useful to someone.
Bob

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: