Обсуждение: 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
>