title: Redshift - Adding dates (month interval) between two dates
body: <p>Using Amazon Redshift.
  Also have a dates table with all calendar dates that can be utilized.</p>
  <p>Question: How can I take a start timestamp (created_at) and end timestamp (ended_at) and add a column that adds 1 month to the start timestamp until the end timestamp.</p>
  <p>I have a table with:</p>
  <ul>
  <li>user_id,</li>
  <li>plan_id,</li>
  <li>created_at,</li>
  <li>ended_at, (can be null)</li>
  </ul>
  <p><img src="https://i.stack.imgur.com/khNOh.png" alt="example record" /></p>
  <p>So if I had a created_at timestamp of 2019-07-11, I would have a column with additional rows for 2019-08-11, 2019-09-11, 2019-10-11, etc. The goal is to associate the monthly amounts paid by a user to the dates when starting with only a start and end date.</p>
  <p>EDIT:</p>
  <p>I used the below query which works when an ended_at timestamp is present, however, when it is null, I need to have the next month populated until an ended_at timestamp is present.</p>
  <pre><code>select
  ps.network_id,
  ps.user_id,
  ps.plan_id,
  ps.created_at,
  extract('day' from ps.created_at) as extract_day,
  d.calendar_date,
  ps.archived_at as ended_at,
  ps.application_fee_percent,
  pp.amount,
  pp.interval,
  pp.name
  from payments_subscriptions ps
  left outer join dates d on extract('day' from date_trunc('day',d.calendar_date)) = extract('day' from ps.created_at) AND date_trunc('day',d.calendar_date) &gt;= date_trunc('day',ps.created_at) AND date_trunc('day',d.calendar_date) &lt; date_trunc('day',ps.archived_at) 
  left outer join payments_plans pp on ps.plan_id = pp.id
  where ps.network_id = '1318990'
  and ps.user_id = '2343404'
  order by 3,6 desc
  </code></pre>
  <p><a href="https://i.stack.imgur.com/XbQRN.png" rel="nofollow noreferrer">output from above query - subscription with null ended_at needs to continue until ended_at is present</a></p>