Re: [GENERAL] Business days
On 26/04/07, Greg Sabino Mullane [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 However, you quickly run into the problem of holidays. While you could construct a helper table listing all the holidays, ones that don't fall on the same day every year (e.g. Easter) will trip you up. Er, isn't Easter usually on a Sunday? I meant the same numerical date, e.g. Christmas is always December 25th, and so is a little easier programatically than the rules for Easter. If you meant that Sunday is never a business day, then yes, it was a bad example. :) Anyway, I also found this, the first hit if you google sql holidays: http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html The big ugly union might need to be munged a bit, but most of the non- weekend US holidays seem to be there. Sure, that's an alternative, but it seems a bit too much reinventing an already existing wheel. I was amused to see the script had the ill-fated Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that describes the rules for each holiday, and then a function that reads it on the fly. Perhaps a project for another day... More complicated than that Easter read Good Friday and Easter Monday. Christmas Eve (does it count or not) Christmas Day, Boxing Day if it falls on a Weekend, Bank holidays are applied in loo on the following monday and tuesday as necessary. There are some quite good list available but you will have to work out what your local logic actually is. Peter. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Business days
am Fri, dem 27.04.2007, um 8:18:55 +0100 mailte Peter Childs folgendes: Sure, that's an alternative, but it seems a bit too much reinventing an already existing wheel. I was amused to see the script had the ill-fated Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that describes the rules for each holiday, and then a function that reads it on the fly. Perhaps a project for another day... More complicated than that Easter read Good Friday and Easter Monday. Some times ago, i have written a little function to calculate easter and other feasts. http://a-kretschmer.de/diverses.shtml HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Business days
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
- -- I need to get the a total number of business days (from monday to
- -- friday) between two dates.
- -- Someone can help me please.
A simplistic approach that counts a business day as being Monday
through Friday would be something like this:
CREATE OR REPLACE FUNCTION bizdays(date,date)
RETURNS BIGINT
LANGUAGE SQL AS
$_$
SELECT count(*) FROM
(SELECT extract('dow' FROM $1+x) AS dow
FROM generate_series(0,$2-$1) x) AS foo
WHERE dow BETWEEN 1 AND 5;
$_$;
CREATE OR REPLACE FUNCTION bizdays(text,text)
RETURNS BIGINT LANGUAGE SQL AS
$_$
SELECT bizdays($1::date,$2::date);
$_$;
SELECT bizdays('20070401','20070407');
However, you quickly run into the problem of holidays. While you
could construct a helper table listing all the holidays, ones that
don't fall on the same day every year (e.g. Easter) will trip
you up. A possible solution is to write a plperlu function that
makes a call to Date::Manip, which can tell you the number of
business days between two date while excluding holidays, and which
allows you to specify exactly which days are considered a holiday.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200704261426
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFGMO9ivJuQZxSWSsgRA8HXAKDSY7vylo/EqQ+fjjwvlrJDdg/S2QCfcaM9
OKi3YW1IWOAc0Nfi9xBjuTc=
=aIqg
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Business days
- -- I need to get the a total number of business days (from monday to - -- friday) between two dates. - -- Someone can help me please. A simplistic approach that counts a business day as being Monday through Friday would be something like this: However, you quickly run into the problem of holidays. While you could construct a helper table listing all the holidays, ones that don't fall on the same day every year (e.g. Easter) will trip you up. Er, isn't Easter usually on a Sunday? Anyway, I also found this, the first hit if you google sql holidays: http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html The big ugly union might need to be munged a bit, but most of the non- weekend US holidays seem to be there. - John D. Burger MITRE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Business days
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 However, you quickly run into the problem of holidays. While you could construct a helper table listing all the holidays, ones that don't fall on the same day every year (e.g. Easter) will trip you up. Er, isn't Easter usually on a Sunday? I meant the same numerical date, e.g. Christmas is always December 25th, and so is a little easier programatically than the rules for Easter. If you meant that Sunday is never a business day, then yes, it was a bad example. :) Anyway, I also found this, the first hit if you google sql holidays: http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html The big ugly union might need to be munged a bit, but most of the non- weekend US holidays seem to be there. Sure, that's an alternative, but it seems a bit too much reinventing an already existing wheel. I was amused to see the script had the ill-fated Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that describes the rules for each holiday, and then a function that reads it on the fly. Perhaps a project for another day... - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200704261706 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD4DBQFGMRUnvJuQZxSWSsgRAwn3AJ9o1CCb2x3Asn1U70xyphetZ6a2XgCY5fuG coAVQiUyFWqKyJWCpJBanA== =gmZi -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Business days
Hello... I need to get the a total number of business days (from monday to friday) between two dates. Someone can help me please. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Business days
On Wed, 25 Apr 2007, Eddy D. Sanchez wrote: I need to get the a total number of business days (from monday to friday) between two dates. Someone can help me please. Joe Celko's SQL for Smarties, 2nd Edition has exactly this solution. Check it out! Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Business days
Eddy D. Sanchez wrote: Hello... I need to get the a total number of business days (from monday to friday) between two dates. Someone can help me please. Whether you're using PHP or not, this page may be of some help: http://www.php.net/manual/en/function.date.php There are a couple of examples there of how to do it. Maybe something there can be of some use. b ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Business days
am Wed, dem 25.04.2007, um 21:01:13 -0400 mailte Eddy D. Sanchez folgendes:
Hello...
I need to get the a total number of business days (from monday to
friday) between two dates.
Someone can help me please.
For instance the number of business days between 2007-04-01 and
2007-04-30:
select sum(case when extract (dow from foo) in(1,2,3,4,5) then 1 else 0 end)
from (select ('2007-04-01'::date + (generate_series(0,'2007-04-30'::date -
'2007-04-01'::date)||'days')::interval) as foo) foo;
Unregardedly Easter!
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
