Django aggregate field, but filtered by date

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!

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.

Django aggregate field, but filtered by date

Related

Recent Posts