Thread: workday function
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
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
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
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/
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
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 __________________________________________________
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 >
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-012007-04-062007-04-092007-05-072007-05-282007-08-272007-12-252007-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