-----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 greg@turnstep.com
PGP Key: 0x14964AC8 200704261426
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFGMO9ivJuQZxSWSsgRA8HXAKDSY7vylo/EqQ+fjjwvlrJDdg/S2QCfcaM9
OKi3YW1IWOAc0Nfi9xBjuTc=
=aIqg
-----END PGP SIGNATURE-----