Postgres generate_series excluding date ranges

First you create a time series of dates over the next two years, EXCEPT your blackout dates:

SELECT dtFROM generate_series('2015-08-01'::date, '2017-08-01'::date, interval '1 day') AS s(dt)EXCEPTSELECT dtFROM generate_series('2015-08-27'::date, '2015-09-03'::date, interval '1 day') as ex1(dt)

Note that you can have as many EXCEPT clauses as you need. For individual blackout days (as opposed to ranges) you could use a VALUES clause instead of a SELECT.

Then you window over that time-series to generate row numbers of billable days:

SELECT row_number() OVER (ORDER BY dt) AS rn, dtFROM (<query above>) x

Then you select those days where you want to bill:

SELECT dtFROM (<query above>) yWHERE rn % 30 = 1; -- billing on the first day of the period

(This latter query following Craig's advice of billing by 30 days)

Yields:

SELECT dtFROM (  SELECT row_number() OVER (ORDER BY dt) AS rn, dt  FROM (    SELECT dt    FROM generate_series('2015-08-01'::date, '2017-08-01'::date, interval '1 day') AS s(dt)    EXCEPT    SELECT dt    FROM generate_series('2015-08-27'::date, '2015-09-03'::date, interval '1 day') as ex1(dt)  ) x) yWHERE rn % 30 = 1;

You will have to split the call to generate series for exclusions. Some thing like this:

• Union of 3 queries
• First query pulls dates from start to exclusion range from
• Second query pulls dates between exclusion range to and your end date
• Third query pulls dates when none of your series dates cross exclusion range

Note: You still need a way to loop through exclusion list (if you have one). Also this query may not be very efficient as such scenarios can be better handled through functions or procedural code.