CODEWITHSUNDEEP
sqlpostgresqldaterangedate-range
simo9900
simo9900

Reputation: 133

Query date ranges excluding weekends in PostgreSQL

I have the following postgresql table;

 id   |        date_slot        
------+-------------------------
 1    | [2023-02-08,2023-02-15)
 2    | [2023-02-20,2023-02-26)
 3    | [2023-02-27,2023-03-29)

I want to make a query that return rows contained in these ranges but exclude weekends

for example the query I made return the following but does not exclude the weekends.

SELECT * FROM table where '2023-02-11'::date <@ date_slot;
 id   |        date_slot        
------+-------------------------
 1    | [2023-02-08,2023-02-15)

2023-02-11 is a weekend so it must not return a result. How can I do that?

Upvotes: 1

Views: 745

Answers (1)

Zegarek
Zegarek

Reputation: 25953

Selecting workday-only dateranges (without weekends):

You can check what day of the week it is on the first day in the range using extract() and knowing its length from upper()-lower(), determine if it'll cross a weekend: online demo

select * 
from test_table 
where '2023-02-11'::date <@ date_slot
and extract(isodow from lower(date_slot)
                        + (not lower_inc(date_slot))::int)
  +(  (upper(date_slot) - (not upper_inc(date_slot))::int)
     -(lower(date_slot) + (not lower_inc(date_slot))::int) ) < 6 ;

Cases where your ranges have varying upper and lower bound inclusivity are handled by lower_inc() and upper_inc() - their boolean result, when cast to int, just adds or subtracts a day to account for whether it's included by the range or not.

The range is on or includes a weekend if it starts on a weekend day or continues for too long from any other day of the week:

  • 4 days, if it starts on a Monday (isodow=1)
  • 3 days, if it starts on a Tuesday (isodow=2)
  • 2 days, if it starts on a Wednesday (isodow=3)
  • 1 days, if it starts on a Thursday (isodow=4)
  • 0 days, if it starts on a Friday (isodow=5)

This means the isodow of range start date and the range length cannot sum up to more than 5 for the range not to overlap a weekend.

You can also enumerate the dates contained by these ranges using generate_series() and see if they include a Saturday (day 6) or a Sunday (0 as dow, 7 as isodow):

select * 
from test_table 
where '2023-02-11'::date <@ date_slot
and not exists (
  select true
  from generate_series(
          lower(date_slot) + (not lower_inc(date_slot))::int,
          upper(date_slot) - (not upper_inc(date_slot))::int,
          '1 day'::interval) as alias(d)
  where extract(isodow from d) in (6,7) );

Selecting records based on workday-only dates:

First comment got it right

select * 
from table_with_dateranges dr, 
     table_with_dates d
where d.date <@ dr.date_slot
and extract(isodow from d.date) not in (6,7);

Upvotes: 1

Related Questions