Select COUNT of days between two dates except weekends

Question

I am trying to get the number of days between two different dates, except the weekends.

I don't know how to achieve this result.

Answer 1

Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10'
                     , interval  '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

• You don't need an extra subquery level for generate_series(). SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause.

• Note in particular that generate_series() includes the upper bound in the output, as long as a full interval (3rd parameter) fits. The upper bound is only excluded if the last interval would be truncated, which is not the case with full days.

• With the pattern ISODOW for EXTRACT(), Sundays are reported as 7, according to the ISO standard. Allows for a simpler WHERE condition.

• Rather call generate_series() with timestamp input. Here is why:

  • Generating time series between two dates in PostgreSQL

• count(*) is slightly shorter and faster than count(the_day), doing the same in this case.

To exclude lower and / or upper bound, add / subtract 1 day accordingly. Typically, you might include the lower and exclude the upper bound:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10' - interval '1 day'
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

Answer 2

Assuming that a weekend is Saturday and Sunday, you can use the following SQL.

select count(the_day) from 
    (select generate_series('2014-01-01'::date, '2014-01-10'::date, '1 day') as the_day) days
where extract('dow' from the_day) not in (0,6)

Replace the dates with your choices and the (0,6) with the days of the week you want to exclude.

A few things you need to take note of :-

1. You have not mentioned what version of PostgreSQL you are running. This works on 9.1+ but should work on lower versions.

2. The chosen dates are inclusive when using generate_series. So if you want days in between then add 1 day to each date.

Answer 3

This example enumerates all dates between 2013-12-15 and 2014-01-02 (inclusively). The second column gives the day of week (numerically, between 0 and 6). The third column marks whether the day of week is a Saturday/Sunday or not (you'll have to adapt what you consider a weekend is) and is what could be used for counting weekdays.

select '2013-12-15'::date + i * interval '1 day',
       extract('dow' from '2013-12-15'::date + i * interval '1 day') as dow,
       case when extract('dow' from '2013-12-15'::date + i * interval '1 day') in (0, 6)
               then false
            else true end as is_weekday
from generate_series(0, '2014-01-02'::date - '2013-12-15'::date) i
;

Answer 4

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

Answer 5

There are a couple of things you can do to make this easier. The method I would use would be to make sure that a table of dates is available. You can quickly create one like so:

CREATE TABLE [dbo].[Dates]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY(0,1),
[Date] Date NOT NULL unique,
isWeekend BIT NOT NULL DEFAULT(0)
)

Once the table is created, you should be able to populate it with date data relatively quickly.

set datefirst 6 --start date is saturday
INSERT INTO dbo.Dates(Date, isWeekend)
select 
    Date,
    case datepart(weekday,date) 
        --relies on DateFirst being set to 6
        when 2 then 1 
        when 1 then 1
        else 0
    end as isWeekend
from (
    select 
        dateadd(day, number - 1, 0) as date
    from (
        SELECT top 100000 row_number() over (order by o.object_id) as number
        FROM sys.objects o
            cross join sys.objects b
            cross join sys.objects c
    )numbers
)data

You can then write your query as a quick count of records from this table.

select count(*) as NumberOfWeekDays
from dbo.dates 
where isWeekend = 0
and date between '1 Jan 2013' and '31 Dec 2013'

Answer 6

-- Returns number of weekdays between two dates
SELECT count(*)  as "numbers of days” 
FROM generate_series(0, (‘from_date’::date - 'todate'::date)) i 
WHERE date_part('dow', 'todate'::date + i) NOT IN (0,6)


-- Returns number of weekdays between two dates
SELECT count(*)  as days 
FROM generate_series(0, ('2014/04/30'::date - '2014/04/01'::date)) i 
WHERE date_part('dow', '2014/04/01'::date + i) NOT IN (0,6)