pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedworkday function
8+ messages / 6 participants
[nested] [flat]
* workday function
@ 2007-05-15 08:51 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 3 replies; 8+ messages in thread
From: Gary Stainburn @ 2007-05-15 08:51 UTC (permalink / raw)
To: pgsql-sql
Hi folks
I need to be able to add and subtract workdays, something like
select CURRENT_DATE - '3 work days'::interval;
I can't see how to do this natively so I'm looking to write a function to do
it and was wondering if anyone's already done it.
While Googling I've found that MS Excel has a workday function which seems to
do what I want.
Any help would be appreciated.
--
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: workday function
@ 2007-05-15 09:09 Richard Huxton <dev@archonet.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2007-05-15 09:09 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary Stainburn wrote:
> Hi folks
>
> I need to be able to add and subtract workdays, something like
>
> select CURRENT_DATE - '3 work days'::interval;
>
> I can't see how to do this natively so I'm looking to write a function to do
> it and was wondering if anyone's already done it.
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.
Casting to interval won't work because work-days will be a variable
amount of real-days based on what you're adding/subtracting from.
> While Googling I've found that MS Excel has a workday function which seems to
> do what I want.
>
> Any help would be appreciated.
Well, you'll be wanting to use extract('dow' from current_date) or
similar to figure out how many days to skip. There are national-holiday
resources online, but I'm not sure if they take into account e.g. the
extra day civil servants get in the UK (or used to) for the Queen's
official birthday.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: workday function
@ 2007-05-15 09:17 Richard Huxton <dev@archonet.com>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2007-05-15 09:17 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
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 :-)
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: workday function
@ 2007-05-15 09:28 Kenneth Gonsalves <lawgon@thenilgiris.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 8+ messages in thread
From: Kenneth Gonsalves @ 2007-05-15 09:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
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
--
regards
kg
http://lawgon.livejournal.com
http://nrcfosshelpline.in/web/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: workday function
@ 2007-05-15 09:44 Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 8+ messages in thread
From: Reinoud van Leeuwen @ 2007-05-15 09:44 UTC (permalink / raw)
To: pgsql-sql
On Tue, May 15, 2007 at 09:51:34AM +0100, Gary Stainburn wrote:
> Hi folks
>
> I need to be able to add and subtract workdays, something like
>
> select CURRENT_DATE - '3 work days'::interval;
Would that take holidays into account? (and wich ones?)
--
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: workday function
@ 2007-05-15 09:46 Dave Page <dpage@postgresql.org>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 0 replies; 8+ messages in thread
From: Dave Page @ 2007-05-15 09:46 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: Richard Huxton <dev@archonet.com>; Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
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
>
We do? First I heard of it!
Regards, Dave
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: workday function
@ 2007-05-15 12:03 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2007-05-15 12:03 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Hello
You can use functions from Orafce package
http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29#PLVdate
sample:
SELECT plvdate.plvdate.default_holydays('czech');
SELECT plvdate.add_bizdays(CURRENT_DATE, 10);
SELECT plvdate.isbizday('2006-12-25');
Regards
Pavel Stehule
2007/5/15, Gary Stainburn <gary.stainburn@ringways.co.uk>:
> Hi folks
>
> I need to be able to add and subtract workdays, something like
>
> select CURRENT_DATE - '3 work days'::interval;
>
> I can't see how to do this natively so I'm looking to write a function to do
> it and was wondering if anyone's already done it.
>
> While Googling I've found that MS Excel has a workday function which seems to
> do what I want.
>
> Any help would be appreciated.
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: workday function
@ 2007-05-15 14:09 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Gary Stainburn @ 2007-05-15 14:09 UTC (permalink / raw)
To: pgsql-sql
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;
end of thread, other threads:[~2007-05-15 14:09 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-15 08:51 workday function Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-05-15 09:09 ` Richard Huxton <dev@archonet.com>
2007-05-15 09:28 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2007-05-15 09:17 ` Richard Huxton <dev@archonet.com>
2007-05-15 14:09 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-05-15 09:46 ` Dave Page <dpage@postgresql.org>
2007-05-15 09:44 ` Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
2007-05-15 12:03 ` Pavel Stehule <pavel.stehule@gmail.com>
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