Thread: counting days
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
Perhaps you are trying to count business days? Those vary around the world and you would certainly need a table to hold the holidays and such. If you just want to count the number of non-weekend-days, then get the interval as days and then it's a simple matter of math: 2*daysInterval/7+(daysInterval % 7 == 6 ? 1 : 0) This holds assuming you are using dates and not timestamps- you may wish to subtract 1 depending on what the dates represent. -M On Aug 29, 2006, at 14:35 , 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. > Regards
On þri, 2006-08-29 at 19:35 +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. how about something like: # assuming d id number of days # and dow is day of week of first day (0-7;sun=0) wholeweeks=days div 7 partial=days mod 7 # adjust dow to mon=0,tue=1...sun=6 dow=(dow+6) mod 7 # count 5 weekdays for each whole week wd=5*wholeweeks # add all days of partial week wd=wd+partial # substract 1 if saturday was included if dow+partial>=6 then wd=wd-1 # substract 1 if sunday was included if dow+partial>=7 then wd=wd-1 # now wd is result hope this helps gnari
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@postgresql.org
pgsql-general-own cc
er@postgresql.org
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
----------------------------------------------------------------------------------------------
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@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On þri, 2006-08-29 at 17:32 -0400, AgentM wrote: > Perhaps you are trying to count business days? Those vary around the > world and you would certainly need a table to hold the holidays and > such. If you just want to count the number of non-weekend-days, then > get the interval as days and then it's a simple matter of math: > > 2*daysInterval/7+(daysInterval % 7 == 6 ? 1 : 0) looks like you are calculating the number of weekend days here, not the number of weekdays. looks like you are assuming the interval started on a monday, too. gnari > This holds assuming you are using dates and not timestamps- you may > wish to subtract 1 depending on what the dates represent. > > -M > > On Aug 29, 2006, at 14:35 , 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. > > Regards > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >