CODEWITHSUNDEEP
postgresqlintervalsdayofweek
Ole
Ole

Reputation: 332

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

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?

Upvotes: 3

Views: 7705

Answers (8)

Wagner Cipriano
Wagner Cipriano

Reputation: 1427

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

Upvotes: 0

Florin Asăvoaie
Florin Asăvoaie

Reputation: 918

You might find this really helpful:

CREATE OR REPLACE FUNCTION working_days(date, date) RETURNS INT AS
$$
SELECT COUNT(days)::INT
    FROM generate_series($1, $2, '1 day') AS days
    WHERE EXTRACT(DOW FROM days) NOT IN(0, 6);
$$
LANGUAGE 'sql' IMMUTABLE STRICT;

Upvotes: 2

Ilia Choly
Ilia Choly

Reputation: 18557

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

Upvotes: 0

Kouber Saparev
Kouber Saparev

Reputation: 8135

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');

Upvotes: 5

user80168
user80168

Reputation:

You might find this blogpost helpful: http://www.depesz.com/index.php/2007/12/27/how-many-1sts-of-any-month-were-sundays-since-1901-01-01/

Upvotes: 0

no_one
no_one

Reputation: 1850

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.

Upvotes: 1

araqnid
araqnid

Reputation: 133802

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.

Upvotes: 1

vartec
vartec

Reputation: 134721

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

Upvotes: 1

Related Questions