cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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.
Rate this article:
Version history
Last update:
‎03-02-2023 08:58 AM
Updated by:
Contributors