Django/python date aggregation by month and quarter

Question

I'm writing a feature that requires the average price of an item over different times (week, month, quarter etc.) Here's the model:

class ItemPrice(models.Model):
    item = models.ForeignKey(Item)
    date = models.DateField()
    price = models.FloatField()

This model tracks the price of the item over time, with new Items being added at frequent, but not regular, intervals. Finding the average price over the last week is easy enough:

ItemPrice.objects.filter(item__id = 1)
    .filter(date_lt = TODAY)
    .filter(date_gte = TODAY_MINUS_7_DAYS)
    .filter(date_.aggregate(Avg('value'))

As a week always has 7 days, but what about month and quarter? They have different numbers of days...?

Thanks!

EDIT: The app is for a finance org, 30-day months wont cut it I'm afraid, thanks for the suggestion!

score 2 · Answer 1 · edited Jan 04 '18 at 08:10

import datetime
from dateutil import relativedelta, rrule  

obj = self.get_object()  
datenow = datetime.datetime.now() 

quarters = rrule.rrule(
        rrule.MONTHLY,
        bymonth=(1, 4, 7, 10),
        bysetpos=-1,
        dtstart=datetime.datetime(datenow.year, 1, 1),
        count=5)

    first_day = quarters.before(datenow)
    last_day = (quarters.after(datenow) - relativedelta.relativedelta(days=1))

    quarter = Payment.objects.filter(
        operation__department__cashbox__id=obj.pk,
        created__range=(first_day, last_day)).aggregate(count=Sum('amount'))

inspiration from there

score 2 · Accepted Answer · edited Oct 17 '17 at 15:15

The solution is two-part, first using the aggregation functions of django ORM, the second using python-dateutil.

from dateutil.relativedelta import relativedelta

A_MONTH = relativedelta(months=1)

month = ItemPrice.objects \
    .filter(date__gte = date - A_MONTH) \
    .filter(date__lt = date) \
    .aggregate(month_average = Avg('price'))

month equals:

{'month_average': 40}

It's worth noticing that you can change the key of the month dictionary by changing the .aggregate() param.

dateutil's relativedelta can handle days, weeks, years and lots more. An excellent package, I'll be re-writing my home-grown hax.

score 1 · Answer 3 · answered Feb 01 '11 at 12:09

1

I would go for the 360-day calendar and not worry about these little inaccuracies. Just use the last 30 days for your "last month average" and the last 90 days for your "last quarter average".

answered Feb 01 '11 at 12:09

Tim Pietzcker

328,213
58
503
561

Did you actually read the Wikipedia article I linked to? The 360-day calendar was designed with exactly this purpose in mind. After all, why should an app that shows an average value "over the last month" behave differently on January 31st (averaging over the last 31 days) and on February 1st (averaging over the last 28 days)?? – Tim Pietzcker Feb 01 '11 at 13:32
1

I mean deviations from the Gregorian calendar, not from one month to another. Thank you for pointing out DAYS360 to me, I may be able to use it in the future, but it doesn't answer the question. – 0atman Feb 02 '11 at 10:33

score 1 · Answer 4 · answered Feb 01 '11 at 12:13

1

First of all, are you interested in the past 7 days or the last week? If the answer is the last week, your query is not correct.

If it is past "n" days that concerns you, then your query is correct and I suppose you can just relax and use 30 days for a month and 90 days for a quarter.

answered Feb 01 '11 at 12:13

shanyu

9,536
7
60
68

I'm interested in months and quarters, really. – 0atman Feb 01 '11 at 13:31

Django/python date aggregation by month and quarter

4 Answers4