Re: counting days

On Tue, Aug 29, 2006 at 07:35:27PM +0100, garry saddington wrote:
> I need to count the days between two dates that are not saturdays or
> sundays. I have read the manual and searched the lists but I am
> struggling. I can count the days but am finding difficulty
> excluding sat and sun from the count. I need this without reference
> to any tables. Does anyone have any pointers please.

You can do this with an SQL function. The function below includes
both the start date and end date, but you could adjust it so that it
takes either or neither. You can query it as though it were a table,
e.g.

SELECT * FROM non_weekends_between('2006-08-01'::date, 'today'::date);

or in your case,

SELECT COUNT(*) FROM non_weekends_between('2006-08-01'::date, 'today'::date);

Regards,
David.

CREATE OR REPLACE FUNCTION non_weekends_between(
first_date DATE, /* $1 */
last_date DATE /* $2 */
)
RETURNS SETOF date
LANGUAGE sql
AS
$$
SELECT
$1 + s.i
FROM generate_series(
0,
$2 - $1
) AS s(i)
WHERE
extract(
DOW
FROM
$1 + s.i
) NOT IN (
0, /* Sunday */
6 /* Saturday */
);
$$;
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!