Is there a better way to filter using quarters in django

Using Django
1

I need to get the count of tasks completed for each quarters. Below is my code to filter a model on quarters. But it will query 4 times to get the result i need. Is there any better or django way to do this?

Model.py :

class task(models.Model):
    task = models.CharField(
        max_length=30, unique=True, blank=False, null=False)
    executed_at = models.DateTimeField(null=True, blank=True)
    completed = models.BooleanField(default=False)

    def __str__(self):
        return self.task

views.py :

import pytz
import datetime
@api_view(('GET',))
def summary(request):
   
    q1_start = datetime.datetime(2021, 1, 1, 0, 0, 0, 0, pytz.UTC)
    q1_end = datetime.datetime(2021, 3, 31, 23, 59, 59, 0, pytz.UTC)

    q2_start = datetime.datetime(2021, 4, 1, 0, 0, 0, 0, pytz.UTC)
    q2_end = datetime.datetime(2021, 6, 30, 23, 59, 59, 0, pytz.UTC)

    q3_start = datetime.datetime(2021, 7, 1, 0, 0, 0, 0, pytz.UTC)
    q3_end = datetime.datetime(2021, 9, 30, 23, 59, 59, 0, pytz.UTC)

    q4_start = datetime.datetime(2021, 10, 1, 0, 0, 0, 0, pytz.UTC)
    q4_end = datetime.datetime(2021, 12, 31, 23, 59, 59, 0, pytz.UTC)

    q1_count = task.objects.filter(executed_at__range=(
        q1_start, q1_end), completed=True).count()

    q2_count = task.objects.filter(executed_at__range=(
        q2_start, q2_end), completed=True).count()

    q3_count = task.objects.filter(executed_at__range=(
        q3_start, q3_end), completed=True).count()

    q4_count = task.objects.filter(executed_at__range=(
        q4_start, q4_end), completed=True).count()

    print(q1_count, q2_count, q3_count, q4_count)
    return Response({"q1_count": q1_count, "q2_count": q2_count, "q3_count": q3_count, "q4_count": q4_count, }, status=status.HTTP_200_OK)
2

Would ExtractQuarter work for you?

1 Like
3

If you’re just looking to get the count by quarter, you should be able to add an annotation to assign a field to the result set, with the counts by that annotated field.

Take a look at the examples in the Aggregation Cheat Sheet - I think there’s a sample in there that would apply.

1 Like
4

Yes. It did the job. Thank you Tim !!!

5

Thanks Ken. I’m able to get the intended result using annotate using the link shared by Tim. Here is my solution:

 q_count = task.objects.filter(completed=True).annotate(quarter=TruncQuarter(
        'executed_at')).values('quarter').annotate(count=Count('task'))

which gives the below result :

<QuerySet [{'quarter': datetime.datetime(2021, 1, 1, 0, 0, tzinfo=<UTC>), 'count': 2}, {'quarter': datetime.datetime(2021, 4, 1, 0, 0, tzinfo=<UTC>), 'count': 1}, {'quarter': datetime.datetime(2021, 10, 1, 0, 0, tzinfo=<UTC>), 'count': 1}]>