How to create a weekly summary of dated records in model django

Question

I have a model that has entries for each day. I want to create a queryset with a subtotals (annotates) for a selection of fields in the model.

This is what i have so far.

Here is the model (or some of it!)

class Shiftlog(models.Model):
    date = models.DateField(default=timezone.now)
    onlinetime = models.DurationField()
    trips = models.IntegerField(default=0)
    km = models.IntegerField(default=0)

    def weekcom(self):
        weekcom = self.date - timedelta(days=self.date.weekday())
        return weekcom

Here is the view code that represents what I am trying to achieve.

report = Shiftlog.objects.values('weekcom').annotate(Sum('km'))

Now this does not work. I see from other posts that the annotate functionality only works with model fields.

I appreciate I can create a dictionary of weekcom (weekcommencing rows) and iterate through the model to populate some k,v buckets for each row (weekcom). However, this does not feel right.

I also appreciate that this can be done in SQL directly (I have not gone to this option yet so i don't have the code), but I am trying to avoid this option too.

Any guidance, much appreciated. Thanks!

***** Update.

Thank you both for your responses. Both were very helpful in different ways. With your help I have solved my problem.

Here is my solution.

data = Shiftlog.objects.values('date__iso_year','date__week').annotate(Sum('km'))

for entry in data:
   entry.update({'week_startdate':
        _startweekdate(entry['date__iso_year'],entry['date__week'])})

def _startweekdate(year,weekno):
    weekstartdate = datetime.strptime(str(year)+'-W'+str(weekno)+'-1', "%G-W%V-%w").date()
    return weekstartdate

This yields a nice dictionary I can iterate through for my report given the week start date and the totals.

score 1 · Answer 1 · answered Mar 11 '20 at 06:19

1

from django.db.models.functions import ExtractYear, ExtractWeek


Shiftlog.objects.annotate(
    week=ExtractWeek('date'),   
    year=ExtractYear('date')
).annotate(
     date=F('week')+F('year')
).values(
    'date'
).annotate(
     s=Sum('km')
).values(
    'date',
    's'
).order_by(
   'date'
)

answered Mar 11 '20 at 06:19

Andrey Nelubin

3,084
1
20
25

Thanks Andrey, without your input I would not have been turned on to the models.functions. – Paul West Mar 13 '20 at 02:12

score 0 · Accepted Answer · answered Mar 11 '20 at 05:35

0

Try this

Shiftlog.objects.values('date__week').annotate(Sum('km'))

answered Mar 11 '20 at 05:35

weAreStarsDust

2,634
3
10
22

How to create a weekly summary of dated records in model django

2 Answers2