businessday.sql

-- -*-sql-*-

drop table holidays;

begin;

create table holidays (
  day date not null,
  name text not null
) without oids;

create index holidays_idx on holidays(day);

-- This is a weekday, Monday-Friday
create or replace function is_weekday(date) returns boolean as '
  select extract(dow from $1) between 1 and 5;
' language sql immutable strict;

-- This is a weekend, Saturday or Sunday
create or replace function is_weekend(date) returns boolean as '
  select not is_weekday($1);
' language sql immutable strict;

-- This is a non-workday
create or replace function is_not_workday(date) returns boolean as '
  select is_weekend($1) or exists (select 1 from holidays where day = $1);
' language sql stable strict;

-- This is a workday
create or replace function is_workday(date) returns boolean as '
  select not is_not_workday($1);
' language sql stable strict;

-- Horribly inefficient - could be replaced easily with a single select
-- on holidays and some logic around the starting and ending DOW
create or replace function business_days_between(date, date) returns integer as '
declare
  start_day alias for $1;
  end_day alias for $2;
  day date := start_day;
  business_days integer := 0;
begin
  while day <= end_day loop
    if is_workday(day) then
      business_days := business_days + 1;
    end if;
    day := day + 1;
  end loop;
  return business_days;
end;
' language plpgsql stable strict;

create or replace function business_hours_between(timestamp, timestamp, time, time) returns interval as '
declare
  start_day date;
  start_time time;
  end_day date;
  end_time time;
  clockin alias for $3;
  clockout alias for $4;
  business_hours interval := 0;
begin
  start_day := $1;
  start_time := $1;
  end_day := $2;
  end_time := $2;

  if start_time < clockin then
    start_time := clockin;
  end if;
  if start_time >= clockout then
    start_time := clockin;
    start_day := start_day + 1;
  end if;
  if end_time > clockout then
    end_time := clockout;
  end if;
  if end_time <= clockin then
    end_time := clockout;
    end_day := end_day - 1;
  end if;
  if end_day < start_day then
    return 0;
  end if;
  if end_day = start_day then
    if is_workday(start_day) then
      return end_time - start_time;
    end if;
    return 0;
  end if;
  if is_workday(start_day) then
    business_hours := business_hours + (clockout - start_time);
  end if;
  if is_workday(end_day) then
    business_hours := business_hours + (end_time - clockin);
  end if;
  return business_hours + (clockout - clockin) * business_days_between(start_day + 1, end_day - 1);
end;
' language plpgsql stable strict;

-- Find the first Monday on or after the given date
create or replace function _first_monday(timestamp) returns date as '
  select $1::date + ((8 - extract(dow from $1)::integer) % 7);
' language sql immutable strict;

-- Find the first Thursday on or after the given date
create or replace function _first_thursday(timestamp) returns date as '
  select $1::date + ((11 - extract(dow from $1)::integer) % 7);
' language sql immutable strict;

-- Return the following Monday for a Sunday, the preceding Friday for a Saturday
create or replace function _avoid_weekend(timestamp) returns date as '
  select $1::date + ((3 - extract(dow from $1)::integer) / 3);
' language sql immutable strict;

create or replace function populate_holidays(integer) returns void as '
declare
  year alias for $1;
  years interval;
  jan1 date;
  king date;
  washington date;
  memorial date;
  july4 date;
  labor date;
  veterans date;
  columbus date;
  thanksgiving date;
  xmas date;
  dow integer;
begin
  years := year * ''1 year''::interval;
  jan1 := _avoid_weekend(''0000-01-01''::date + years);
  king := _first_monday(''0000-01-22''::date + years);
  washington := _first_monday(''0000-02-22''::date + years);
  memorial := _first_monday(''0000-05-25''::date + years);
  july4 := _avoid_weekend(''0000-07-04''::date + years);
  labor := _first_monday(''0000-09-01''::date + years);
  veterans := _avoid_weekend(''0000-11-11''::date + years);
  columbus := _first_monday(''0000-10-08''::date + years);
  thanksgiving := _first_thursday(''0000-11-22''::date + years);
  xmas := _avoid_weekend(''0000-12-25''::date + years);
  insert into holidays (day, name) values (jan1, ''New Years Day'');
  insert into holidays (day, name) values (king, ''Martin Luther King, Jr Day'');
  insert into holidays (day, name) values (washington, ''Washington'''' Birthday'');
  insert into holidays (day, name) values (memorial, ''Memorial Day'');
  insert into holidays (day, name) values (july4, ''Independence Day'');
  insert into holidays (day, name) values (labor, ''Labor Day'');
  insert into holidays (day, name) values (veterans, ''Veterans Day'');
  insert into holidays (day, name) values (columbus, ''Columbus Day'');
  insert into holidays (day, name) values (thanksgiving , ''Thanksgiving'');
  insert into holidays (day, name) values (xmas, ''Christmas Day'');
  return;
end;
' language plpgsql;

select populate_holidays(2007);
select populate_holidays(2008);
select populate_holidays(2009);
select populate_holidays(2010);
select populate_holidays(2011);
select populate_holidays(2012);
select populate_holidays(2013);
select populate_holidays(2014);
select populate_holidays(2015);
select populate_holidays(2016);

commit;