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
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;
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
