This post is from one of our customers!
I work for a company that uses a subscription revenue model, where subscriptions are stored in postgres as start and end timestamps. For our insurance & billing department, it's important to know how many assets were actually out on any day of the week, but there are a couple issues:
- The date ranges for a subscription are variable from 1 to 28+ days
- The start and end timestamps can be retroactively changed by ops
If you're starting with a start and end timestamp (e.g. '2017-09-05' & '2017-09-15') and want to extrapolate all rows between those ('2017-09-05... '09-06'...'09-07'...'09-08'...)
Start by generating a table of dates in a given range,
FROM ( SELECT
date( generate_series('2017-09-01'::timestamp, now(), '1 day') as days
) as all_dates
Then left join in your events table on both the start and end dates.
LEFT JOIN ( SELECT
e.sub_id
,e.start --example: '2017-09-05'
,e.end --example: '2017-09-15'
FROM events e
) as subs
ON (subs.end >= all_dates.days) AND (subs.start <= all_dates.days)
This leaves you with an extrapolated table of all the days between the two dates for quick and easy aggregation.
