Django aggregate field, but filtered by date

Question

I,m trying to annotate a sum of another model, but filtered by date.

I have the models Employee and Shift, the Shift one has a DecimalField called dur, a DateTimeField start and a foreign key employee.

class Employee(models.Model):
    name = models.CharField(max_length=64)

class Shift(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    start = models.DateTimeField()
    dur = models.DecimalField(max_digits=4, decimal_places=2, default=0)

With Employee.objects.all().annotate(Sum('shift__dur')) I could add the total of all shifts, but how can I filter these shifts, e.g. to sum just the shifts in a given date range?

Thanks in advance!

Can you share the relevant models (or at least the relevant parts of these models)? — Willem Van Onsem, Jan 22 '22 at 20:24
@WillemVanOnsem The relevant parts are described in the second sentence :) — Jemx, Jan 22 '22 at 20:26
yes, but usually it is easier to work with model definitions, since these can be copied in a "sandbox Django environment" — Willem Van Onsem, Jan 22 '22 at 20:27

Willem Van Onsem · Accepted Answer · 2022-01-22T21:34:52.667

You can filter for example with:

Employee.objects.filter(
    shift__start__range=('2022-01-01', '2022-01-31')
).annotate(
    total=Sum('shift__dur')
)

This will only show shifts for January 2022. Furthermore Employees without any shift for January 2022 will be filtered out.

You can also obtain data per week, for example with:

from django.db.models.functions import TruncWeek

Shift.objects.values(
    'employee_id',
    week=TruncWeek('start')
).annotate(
    total=Sum('dur')
).order_by('employee_id', 'week')

This will construct a QuerySet with:

<QuerySet [
    {'employee_id': 1, 'week': date(2022, 1, 3), 'total': Decimal('14.25')},
    {'employee_id': 1, 'week': date(2022, 1, 10), 'total': Decimal('13.02')},
    {'employee_id': 1, 'week': date(2022, 1, 17), 'total': Decimal('17.89')},
    {'employee_id': 2, 'week': date(2022, 1, 3), 'total': Decimal('57.3')},
    {'employee_id': 2, 'week': date(2022, 1, 17), 'total': Decimal('18.30')}
]>

This thus makes an aggregate per empoloyee primary key and per week (the week is truncated to start on monday), and the total lists the total of the durs of the shifts. If for a given employee_id-week combination, there are no shifts, then this will not appear in the queryset. You thus will need to do some post-processing if you want to introduce zeros for these weeks.

Thank you! I think you ment `'2022-01-31'` instead of `'2020-01-31'` in your first example — Jemx, Jan 22 '22 at 21:33

Django aggregate field, but filtered by date

1 Answers1