If I understood you correctly, you want the number of weekdays (e.g. an
integer number of days) between 2 specified dates.
This seems to work (although I am not the greatest plpgsql function person
- not enough practice - there may be better ways):
CREATE or REPLACE FUNCTION count_weekdays (date, date) returns integer
language plpgsql STABLE
AS '
DECLARE
start_date alias for $1;
end_date alias for $2;
tmp_date date;
tmp_dow integer;
-- double precision returned from extract
tot_dow integer;
BEGIN
tmp_date := start_date;
tot_dow := 0;
WHILE (tmp_date <= end_date) LOOP
select into tmp_dow cast(extract(dow from tmp_date) as integer);
IF ((tmp_dow >= 2) and (tmp_dow <= 6)) THEN
tot_dow := (tot_dow + 1);
END IF;
select into tmp_date (tmp_date + interval ''1 day '');
END LOOP;
return tot_dow;
END;
';
select count_weekdays(date '2006-08-01', date '2006-08-10');
count_weekdays
----------------
8
(1 row)
Note that I used 2 single-quotes around ''1 day'', not double quotes. I'm
on version 7.4.6.
Susan Cassidy
garry saddington
<garry@schoolteac
hers.co.uk> To
Sent by: pgsql-general@xxxxxxxxxxxxxx
pgsql-general-own cc
er@xxxxxxxxxxxxxx
Subject
[GENERAL] counting days
08/29/2006 11:35
AM
|-------------------|
| [ ] Expand Groups |
|-------------------|
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.
Regards
Garry
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------
