Redshift - Adding dates (month interval) between two dates

0
votes

Using Amazon Redshift. Also have a dates table with all calendar dates that can be utilized.

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.

I have a table with:

  • user_id,
  • plan_id,
  • created_at,
  • ended_at, (can be null)

example record

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.

EDIT:

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.

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) >= date_trunc('day',ps.created_at) AND date_trunc('day',d.calendar_date) < 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

output from above query - subscription with null ended_at needs to continue until ended_at is present

2
sqlamazon-redshift

2 Answers

0
votes

Use dateadd function for increasing time/date in timestamp https://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html

For increasing one month use this:

DATEADD(month, 1, CURRENT_TIMESTAMP)

0
votes

For anyone looking for a potential solution, I ended up joining my dates table in this fashion:

LEFT OUTER JOIN dates d ON extract('day' FROM date_trunc('day',d.calendar_date)) = extract('day' FROM payments_subscriptions.created_at)
                            AND date_trunc('day',d.calendar_date) >= date_trunc('day',payments_subscriptions.created_at)
                            AND date_trunc('day',d.calendar_date) < date_trunc('day',getdate())

and this where clause:

WHERE (calendar_date < date_trunc('day',payments_subscriptions.archived_at) OR payments_subscriptions.archived_at is null)