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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI am trying to get the number of days between two different dates, except the weekends.
I don't know how to achieve this result.
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:
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;
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 :-
You have not mentioned what version of PostgreSQL you are running. This works on 9.1+ but should work on lower versions.
The chosen dates are inclusive when using generate_series. So if you want days in between then add 1 day to each date.
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
;
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'
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
-- 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)