From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: workday function
Date: Tue, 15 May 2007 15:09:40 +0100
Message-ID: <200705151509.41320.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <46497A96.7070901@archonet.com>
References: <200705150951.34928.gary.stainburn@ringways.co.uk>
<222A6AC0-8FA5-4CDD-93C6-DA0A7A255EF7@thenilgiris.com>
<46497A96.7070901@archonet.com>
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
Attachments:bank_holidays.sql (text/plain, 3.2K) download
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: workday function
In-Reply-To: <200705151509.41320.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox