Getting number of certain days-of-the-week (weekend) from interval in PostgreSQL

0 votes
asked Apr 14, 2009 by ole

Given 2 timestamps in postgres, how do you calculate the time difference without counting whole Saturdays and Sundays?

OR

How do you count the number of Saturdays and Sundays in a given time interval?

6 Answers

0 votes
answered Jan 14, 2009 by araqnid

This should answer the second part of your question:

create or replace function is_weekend_day(date) returns boolean
 strict immutable language 'sql'
 as $$ select case extract(dow from $1) when 0 then true when 6 then true else false end $$;

create or replace function count_weekend_days(start_date date, end_date date) returns int
 strict immutable language 'sql'
 as $$
select cast(sum(case when is_weekend_day($1 + ofs) then 1 else 0 end) as int)
from generate_series(0, $2 - $1) ofs
$$;

Making a corresponding count_non_weekend_days is simple after that.

0 votes
answered Jan 14, 2009 by no-one

The best solution to this will be to use a calendar table. This is incredibly useful and you can do all sort of interesting things - including counting the number of working days between two dates or counting the number of holidays between two dates.

This table is usually populated in advance - say for 20 years with the date for well known holidays appropriately tagged. If holidays shift, you maintain the table once in a while to mark the days as holidays. More info here and here. This uses MS SQL Server, but is easily ported as well.

0 votes
answered Apr 14, 2009 by vartec

(days/7)*2 + number of sat/sun in the last (days%7) days.

0 votes
answered Apr 15, 2009 by kouber-saparev

The following function is returning the number of full weekend days between two dates. As you need full days, you can cast the timestamps to dates before calling the function. It returns 0 in case the first date is not strictly before the second.

CREATE FUNCTION count_full_weekend_days(date, date)
  RETURNS int AS
$BODY$
  SELECT
    ($1 < $2)::int
      *
    (
      (($2 - $1) / 7) * 2
        + 
      (EXTRACT(dow FROM $1)<6 AND EXTRACT(dow FROM $2)>0 AND EXTRACT(dow FROM $1)>EXTRACT(dow FROM $2))::int * 2
        +
      (EXTRACT(dow FROM $1)=6 AND EXTRACT(dow FROM $2)>0)::int
        +
      (EXTRACT(dow FROM $2)=0 AND EXTRACT(dow FROM $1)<6)::int
    );
$BODY$
  LANGUAGE 'SQL' IMMUTABLE STRICT;

Examples:

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-10', '2009-04-20');
# returns 4

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-11', '2009-04-20');
# returns 3 (11th is Saturday, so it shouldn't be counted as full day)

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-12', '2009-04-20');
# returns 2 (12th is Sunday, so it shouldn't be counted as full day)

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-13', '2009-04-20');
# returns 2

To obtain the number of days except full weekend days, simply subtract the number of days from the function above:

SELECT
  '2009-04-20'::date
    -
  '2009-04-13'::date
    -
   COUNT_FULL_WEEKEND_DAYS('2009-04-13', '2009-04-20');
0 votes
answered Jan 2, 2014 by ilia-choly

This will count the number of a certain day between two dates:

-- 0 Sunday
-- 1 Monday
-- 2 Tuesday
-- 3 Wednesday
-- 4 Thursday
-- 5 Friday
-- 6 Saturday

WITH rng AS (
  SELECT
      'march 3 2013'::date AS start,
      'march 3 2014'::date AS end,
      0                    AS day -- Sunday
)

SELECT count(1)
FROM rng, generate_series(0, (extract(epoch from age(rng.end, rng.start)) / (60*60*24))::int) AS n
WHERE extract(dow from rng.start + (n * '1 day'::interval)) = rng.day
0 votes
answered Jan 29, 2015 by wagner-cipriano

I suggest you to create a function for use whenever you want and write less ; )

This code above will create a sql function that count and return the amount of weekend days (Sat, Sun) . Just the way you will have more flexibility to use this function.

CREATE OR REPLACE FUNCTION <YourSchemaNameOptional>.count_full_weekend_days(date, date)
RETURNS bigint AS
$BODY$
        select  COUNT(MySerie.*) as Qtde
        from    (select  CURRENT_DATE + i as Date, EXTRACT(DOW FROM CURRENT_DATE + i) as DiaDate
                 from    generate_series(date ($1) - CURRENT_DATE,  date ($2) - CURRENT_DATE ) i) as MySerie
        WHERE   MySerie.DiaDate in (6,0);
$BODY$
LANGUAGE 'SQL' IMMUTABLE STRICT;

After that, you can use the function to return only the number of weekend days in a interval. Here's the example to use:

SELECT <YourSchemaNameOptional>.count_full_weekend_days('2017-09-11', '2017-09-25') as days; --> RES: 4

This select must return four because the first and the second day are Monday, and we have 2 Saturdays and 2 Sundays between them.

Now, to return only business days (without weekends), as you want, just make a subtraction, like the example below:

SELECT (date '2017-09-25' - date '2017-09-11' ) - <YourSchemaName>.count_full_weekend_days('2017-09-11', '2017-09-25'); --> RES: 14 - 4 = 10
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...