Get the number of remaining days after excluding date ranges in a table

Question

create table test (start date ,"end" date);

insert into test values 
      ('2019-05-05','2019-05-10')
     ,('2019-05-25','2019-06-10')
     ,('2019-07-05','2019-07-10')
     ;

I am looking for the following output, where for every date between the start and end the person is available only between start and end. considering for the month of may he is present for 11 days(05/05 to 05/10 and 05/25 to 05/31) and the total number of days in the month of may is 31. The output column should have 31-11 (the number of days he worked)

MonthDate------Days-

2019-05-01   20(31-11)
2019-06-01   20(30-10)
2019-07-01   26(31-5)

I don't believe your results. Your description leaves 18 free days in May, for instance. — Gordon Linoff, Dec 05 '19 at 00:16
You'll have to decide whether to include or exclude the upper bound of ranges - consistently. — Erwin Brandstetter, Dec 05 '19 at 00:17

score 1 · Answer 1 · answered Dec 05 '19 at 00:11

I get slightly different results.

But the idea is to generate every date. Then filter out the ones that are used and aggregate:

select date_trunc('month', dte) as yyyymm,
       count(*) filter (where t.startd is null) as available_days
from (select generate_series(date_trunc('month', min(startd)), date_trunc('month', max(endd)) + interval '1 month - 1 day', interval '1 day') dte
      from test
     ) d left join
     test t
     on d.dte between t.startd and t.endd
group by date_trunc('month', dte)
order by date_trunc('month', dte);

Here is a db<>fiddle.

The free days in May are:

I am counting 18 of these. So, I believe the results from this query.

If you do not want to include the end date (which is contrary to your description using "between", then the on logic would be:

 on d.dte >= t.startd and 
    d.dte < t.endd

But that would only get you up to 19 in May.

Erwin Brandstetter · Answer 2 · 2019-12-05T00:28:59.587

Your results are inconsistent. I decided to go with inclusive bounds for the simplest solution:

SELECT date_trunc('month', d)::date, count(*)
FROM (
   SELECT generate_series(timestamp '2019-05-01', timestamp '2019-07-31', interval '1 day') d
   EXCEPT ALL
   SELECT generate_series(start_date::timestamp, end_date::timestamp, interval '1 day') x
   FROM   test
   ) sub
GROUP  BY date_trunc('month', d);

date_trunc | count
-----------+------
2019-05-01 |    18
2019-06-01 |    20
2019-07-01 |    25

db<>fiddle here

This generates all days of a given time frame (May to July of the year in your case) and excludes the days generated from all your date ranges.

Assuming at least Postgres 10.

What is the expected behaviour for multiple set-returning functions in SELECT clause?

Assuming data type date in your table. I cast to timestamp for best results. See:

Generating time series between two dates in PostgreSQL

Aside: don't use the reserved words start and end as identifiers.

Select rows which are not present in other table

I dont want to hardcode the values. because this is test data. I have to use this query for many records — user10449251, Dec 05 '19 at 03:30

Get the number of remaining days after excluding date ranges in a table

2 Answers2