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.
Please sign in to leave a comment.