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

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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/



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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
__________________________________________________

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hello

You can use functions from Orafce package
http://www.pgsql.cz/index.php/Oracle...8en%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
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster