From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: workday function
Date: Tue, 15 May 2007 15:09:40 +0100
User-Agent: KMail/1.9.5
References: <200705150951.34928.gary.stainburn@ringways.co.uk>
	<222A6AC0-8FA5-4CDD-93C6-DA0A7A255EF7@thenilgiris.com>
	<46497A96.7070901@archonet.com>
In-Reply-To: <46497A96.7070901@archonet.com>
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
  boundary="Boundary-00=_l8bSGTgy8TMU9Vg"
Message-Id: <200705151509.41320.gary.stainburn@ringways.co.uk>

--Boundary-00=_l8bSGTgy8TMU9Vg
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Tuesday 15 May 2007 10:17, Richard Huxton wrote:
> Kenneth Gonsalves wrote:
> > On 15-May-07, at 2:39 PM, Richard Huxton wrote:
> >> Don't know of one - not sure what "workday" would mean in a global
> >> sense. I mean, Mon-Fri in most European office settings, but you'd
> >> include Sat in retail settings and in Islamic countries presumably
> >> exclude Fridays. Our local library shuts early on Mondays iirc but is
> >> open Saturday mornings.
> >
> > and i hear in england people work half day on wednesday
>
> Some shops and banks used to when I was a child. Nowadays many
> supermarkets are open 24 hours (except Sundays - due to legislation)
>
> Of course, I've been in companies where some people working for half the
> day would be a huge improvement :-)

As a first stage I've written a number of functions to calculate the standard 
UK bank holidays, and return these as a dataset.

select * from bank_holidays(2007);
 bank_holidays
---------------
 2007-01-01
 2007-04-06
 2007-04-09
 2007-05-07
 2007-05-28
 2007-08-27
 2007-12-25
 2007-12-26
(8 rows)

I've attached the code for anyone who want to use or improve it.

It should be fairly simple to write a function to use that dataset to ignore 
*non-working* days.

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

--Boundary-00=_l8bSGTgy8TMU9Vg
Content-Type: text/plain;
  charset="iso-8859-1";
  name="bank_holidays.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="bank_holidays.sql"

create or replace function easter_sunday(year integer) returns date as $$
DECLARE
  a integer;
  b integer;
  c integer;
  d integer;
  e integer;
  month integer;
  day integer;
  easter text;
BEGIN
  a := year % 19;
  b := year % 4;
  c := year % 7;
  d := ((a*19)+24) % 30;
  e := ((b*2)+(c*4)+(d*6)+5) % 7;
  IF (d+e < 10) THEN
    month=3;
    day=d+e+22;
  ELSE
    month=4;
    day=d+e-9;
  END IF;
  easter := year || '-' || month || '-' || day;
  return easter::date;
END;
$$ LANGUAGE plpgsql;

create or replace function new_year(year integer) returns date as $$
DECLARE
  day integer;
  wdate text;
BEGIN
  wdate := year || '-01-01';
  select into day extract(DOW from wdate::date);
  IF (day = 0) THEN
    return (year || '-01-02')::date;
  END IF;
  IF (day = 6) THEN
    return (year || '-01-03')::date;
  END IF;
  return (year || '-01-01')::date;
END;
$$ LANGUAGE plpgsql;

create or replace function christmas(year integer) returns date as $$
DECLARE
  day integer;
  wdate text;
BEGIN
  wdate := year || '-12-25';
  select into day extract(DOW from wdate::date);
  IF (day = 0) THEN
    return (year || '-12-26')::date;
  END IF;
  IF (day = 6) THEN
    return (year || '-12-27')::date;
  END IF;
  return (year || '-12-25')::date;
END;
$$ LANGUAGE plpgsql;

create or replace function boxing_day(year integer) returns date as $$
DECLARE
  day integer;
  wdate text;
BEGIN
  wdate := year || '-12-25';
  select into day extract(DOW from wdate::date);
  IF (day = 0) THEN
    return (year || '-12-27')::date;
  END IF;
  IF (day > 4) THEN
    return (year || '-12-28')::date;
  END IF;
  return (year || '-12-26')::date;
END;
$$ LANGUAGE plpgsql;

create or replace function may_day(year integer) returns date as $$
DECLARE
  day integer;
  wdate text;
BEGIN
  wdate := year || '-05-01';
  select into day 9-extract(DOW from wdate::date);
  IF (day > 7) THEN
    day := day-7;
  END IF;
  return (year || '-05-' || day)::date;
END;
$$ LANGUAGE plpgsql;

create or replace function whitsun(year integer) returns date as $$
DECLARE
  day integer;
  wdate text;
BEGIN
  wdate := year || '-05-31';
  select into day extract(DOW from wdate::date);
  IF (day = 0) THEN
    day := 25;
  ELSE
    day := 32-day;
  END IF;
  return (year || '-05-' || day)::date;
END;
$$ LANGUAGE plpgsql;

create or replace function summer_bank(year integer) returns date as $$
DECLARE
  day integer;
  wdate text;
BEGIN
  wdate := year || '-08-31';
  select into day extract(DOW from wdate::date);
  IF (day = 0) THEN
    day := 25;
  ELSE
    day := 32-day;
  END IF;
  return (year || '-08-' || day)::date;
END;
$$ LANGUAGE plpgsql;

create or replace function bank_holidays(year integer) returns setof date as $$
DECLARE
  wdate date;
BEGIN
  select into wdate new_year(year);
  return next wdate;
  select into wdate easter_sunday(year)-'2 days'::interval;
  return next wdate;
  select into wdate easter_sunday(year)+'1 days'::interval;
  return next wdate;
  select into wdate may_day(year);
  return next wdate;
  select into wdate whitsun(year);
  return next wdate;
  select into wdate summer_bank(year);
  return next wdate;
  select into wdate christmas(year);
  return next wdate;
  select into wdate boxing_day(year);
  return next wdate;
  return;
END;
$$ LANGUAGE plpgsql;

--Boundary-00=_l8bSGTgy8TMU9Vg--