SEO

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

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

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

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

-----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=2529DF...9B906714964AC8
-----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/

> - -- 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/Articl...ver_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

-----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/Articl...ver_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 greg@turnstep.com
PGP Key: 0x14964AC8 200704261706
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD4DBQFGMRUnvJuQZxSWSsgRAwn3AJ9o1CCb2x3Asn1U70xyph etZ6a2XgCY5fuG
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

On 26/04/07, Greg Sabino Mullane <greg@turnstep.com> 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/Articl...ver_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

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/