Postgres generate_series excluding date ranges
I am building a subscription management system and need to create a list of upcoming billing dates for the next 2 years. I was able to use generate_series to get the corresponding 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 to exclude certain date ranges from the calculation. These excluded date ranges can be any time range. In addition, they should not count towards the time range for generate_series.
For example, let's say we have a date range exception from '2015-08-27' to '2015-09-03'. The resulting generate_series should exclude this week's date from the calculation and basically push all future monthly calculated dates one week into the future :
2015-08-01 2015-09-10 2015-10-10 2015-11-10 2015-12-10
source to share
First, you create a time series of dates for the next two years, EXCEPT
the shutdown 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)
Please note that you can have as many offers EXCEPT
as you need. For individual shutdown days (as opposed to ranges), you can use the offer VALUES
instead SELECT
.
You will then close this time series to generate billable day line numbers:
SELECT row_number() OVER (ORDER BY dt) AS rn, dt
FROM (<query above>) x
Then you select the days on which you want to invoice:
SELECT dt
FROM (<query above>) y
WHERE rn % 30 = 1; -- billing on the first day of the period
(This last request, following Craig's advice on billing for 30 days)
Productivity:
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;
source to share
You will need to split the call to generate a series for exceptions. Something like that:
- Union of three requests
- The first query pulls dates from start to exclusion range from
- The second query pulls the dates between the exclusion range and the end date.
- The third query outputs dates when none of your serial dates crosses the exception range
Note. You still need a way to iterate over the list of exceptions (if you have one). Also, this request may not be very efficient as such scenarios can be better handled through functions or procedural code.
source to share