DATE_TRUNC function rounding values to the first day of next month an leaving gaps in the data


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:

        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 =
    join plans
        on = 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--,
    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?

Thanks! :slight_smile:

Similar result here, this subscription runs from Feb. 2022 - Feb. 2023 but it starts the grouping on Mar. 2022

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.

@DCamps Try running the SQL query via another client like - if you get the same results, then it's something on the database.

1 Like

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.