CODEWITHSUNDEEP
pythondjangoormdate
0atman
0atman

Reputation: 3374

Django/python date aggregation by month and quarter

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!

Upvotes: 2

Views: 5303

Answers (4)

madjardi
madjardi

Reputation: 5939

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

Upvotes: 2

0atman
0atman

Reputation: 3374

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.

Upvotes: 2

shanyu
shanyu

Reputation: 9716

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.

Upvotes: 1

Tim Pietzcker
Tim Pietzcker

Reputation: 336158

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".

Upvotes: 1

Related Questions