CODEWITHSUNDEEP
postgresqltime-seriesplpgsql
Echchama Nayak
Echchama Nayak

Reputation: 933

Counting number of weekdays between two dates

I am trying to get the number of weekdays between two dates in plpgsql. The following is my code:

CREATE FUNCTION weekdays(DATE, DATE) RETURNS INTEGER AS
$$
DECLARE
    d date := $1;
    weekdays integer := 0
BEGIN

LOOP
    IF select extract(dow from date d) != 6 or select extract(dow from date d) != 0
      weekdays := weekdays +1
    END IF
    d := d + 1;
    EXIT WHEN d > $2;
END LOOP;
RETURN weekdays;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

However, I keep getting the following error:

Unterminated dollar quote started at position 56 in SQL
CREATE FUNCTION weekdays(DATE, DATE) RETURNS INTEGER AS $$ DECLARE d date := $1.
Expected terminating $$

Upvotes: 1

Views: 4713

Answers (3)

Jasen
Jasen

Reputation: 12442

You don't need to loop, or generate a temporary resultset with generate_series(), the answer can be found using only arithmetic.

CREATE OR REPLACE FUNCTION public.weekdays(s date, e date) RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
  sd int = extract (isodow from s-1); 
  ed int = extract (isodow from e); 
BEGIN
   RETURN - case sd when 7 then 5 when 6 then 5 else sd end 
          + case ed when 7 then 5 when 6 then 5 else ed end 
          + (e-ed-s+sd+1)/7*5;
END;
$function$;

Upvotes: 2

sergio
sergio

Reputation: 21

Looks less complex to me and is also working.

CREATE OR REPLACE FUNCTION weekdays
(
date1 DATE,
date2 DATE
)
RETURNS INTEGER AS $$
DECLARE weekdays INTEGER;
BEGIN

SELECT COUNT(extract(ISODOW FROM series)) INTO weekdays
FROM generate_series(date1::date, date2::date, '1 day'::interval) series
WHERE extract(ISODOW FROM series) < 6;

RETURN weekdays;

END;
$$ LANGUAGE plpgsql;

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659187

The error is due to a missing ; after weekdays integer := 0;. Plus several more errors of this kind. This would work:

CREATE  OR REPLACE FUNCTION weekdays(date, date)
  RETURNS integer AS
$func$
DECLARE
   d        date := $1;
   weekdays int  := 0;
BEGIN
   LOOP
      IF extract(isodow from d) < 6 THEN  -- simpler
         weekdays := weekdays + 1;
      END IF;
      d := d + 1;
      EXIT WHEN d > $2;
   END LOOP;
   RETURN weekdays;
END
$func$  LANGUAGE plpgsql IMMUTABLE;

But I suggest this simpler and faster SQL function instead:

CREATE OR REPLACE FUNCTION weekdays_sql(date, date)
  RETURNS integer AS
$func$
SELECT count(*)::int
FROM   generate_series($1, $2, interval '1d') d
WHERE  extract(isodow FROM d) < 6;
$func$  LANGUAGE sql IMMUTABLE;

Further performance improvement for big ranges possible. Related:

Upvotes: 6

Related Questions