Running Postgres. I'm having an issue with the date_trunc() function where it seems to "skip 1 month" when grouping the data. To give you an idea of what I'm trying to do, I need to get the MRR amount for subscriptions grouped by month. So I do the following query:
select
--payola_subscriptions.account_id,
date_trunc('month', payola_sales.created_at) as date,
--payola_sales.created_at as date,
sum(payola_sales.amount) / 100 as mrr
from payola_sales
join payola_subscriptions
on payola_sales.owner_id = payola_subscriptions.id
join plans
on plans.id = payola_sales.product_id
where payola_sales.owner_type = 'StripeSubscription' and payola_sales.state = 'finished' and plans.interval = 'month' and payola_subscriptions.account_id = 16853
--group by payola_subscriptions.account_id, payola_sales.created_at, payola_subscriptions.ended_at, payola_subscriptions.current_period_end, payola_subscriptions.current_period_start, payola_sales.amount, plans.interval--, payola_subscriptions.id
group by date
This yields the following result in the screenshot:
So, for this particular customer, when we look up their subscription data, we can see that they were on a monthly plan of $199USD from Dec. 2016 until Dec. 2019, and then they switched over to a cheaper plan starting on Jan. 25, 2020.
So for the first subscription, I get the correct time frame from Dec. 2016 - Dec. 2019, but when there's a change in plan, there's a gap between Dec. 2019 and Feb. 2020 and instead groups the MRR as though it started in Feb. 2020 instead of Jan. 2020.
Is there a way to tell the date_trunc function not to round up to the first of next month?
Hi @DCamps
Post "Diagnostic Info" from Admin > Troubleshooting.
And what is the exact column type of payola_sales.created_at ?
It's probably easier if you look at the Table visualization instead of Bar.
Hey @flamber thank you for your prompt response. It looks like I don't have Admin permissions in our account so I can't access the Admin setting for the Diagnostic info. Will have to come back to you on that once my Admin is able to get that for me.
To answer your question, we use Stripe as our payments & subscription service. The payola_sales.created_at is a date-time variable that stores the date & time a transaction was made (purchase made in Stripe) for a particular subscription item.
Here's a picture of the table view where I've highlighted the jump from Dic. 2019 to Feb. 2020
However, if you look at the Stripe data for this particular customer that I used in this example, you can see that the last month they were on the 99USD subscription was Dic. 2019, and then started the 79USD subscription in Jan. 25, 2020.
Hey @flamber, thank you for your getting back to me and for your suggestion. Apologies, this actually turned out to be an error on my end. Upon further inspection of the Stripe payment data, it turns out that the invoice for the Jan. 2020 period was refunded for this particular customer. Hence, the query is correct in not displaying the value for that month.
