Postgres generate_series excluding date ranges

Question

I'm creating a subscription management system, and need to generate a list of upcoming billing date for the next 2 years. I've been able to use generate_series to get the appropriate dates as such:

SELECT i::DATE
FROM generate_series('2015-08-01', '2017-08-01', '1 month'::INTERVAL) i

The last step I need to take is exclude specific date ranges from the calculation. These excluded date ranges may be any range of time. Additionally, they should not be factored into the time range for the generate_series.

For example, say we have a date range exclusion from '2015-08-27' to '2015-09-03'. The resulting generate_series should exclude the date that week from the calculation, and basically push all future month billing dates one week to the future:

2015-08-01
2015-09-10
2015-10-10
2015-11-10
2015-12-10

Answer 1

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

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)

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, dt
FROM (<query above>) x

Then you select those days where you want to bill:

SELECT dt
FROM (<query above>) y
WHERE 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 dt
FROM (
  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
) y
WHERE rn % 30 = 1;