Django annotate data by date for empty result

Question

Suppose I have a object model A, and it has a field called created, which is a datetime type field.

If I use annotate to count how many A are created each day, I can use

A.objects.annotate(date=Trunc('created', 'day', output_field=DateField()) ).values('date').order_by('date').annotate(count=Count('id'))

After that, I can get the result, which looks like

[{date: '2018-07-22', count:1 }, {date: '2018-07-23', count:1 }, {date: '2018-07-25', count:1 }]

However, notice that I miss a 2018-07-24 because it didn't create any A in that day. Is there any way to let result to have {date: '2018-07-24', count:0 } inside that queryset?

Dulciedulcify

Have you found a solution for this? thanks – Chaechaeronea

Hi, maybe try this: #52290930 – Kape

Does this answer your question? Django + PostgreSQL: Fill missing dates in a range – Rockbottom

@MarekNaskret the answer there is not applicable because that question is for the case when there are related models. (The “when there is no relation” part of the question title is misleading.) This question is in the context of a single model, and the query can only work with the same model's attributes. – Rockbottom

I think this is what you are looking for #52290930 – Merwyn

Answer 1

My variant for PostgreSQL:

from datetime import date, timedelta
from django.db.models.functions import Trunc
from django.db.models.expressions import Value
from django.db.models import Count, DateField

# A is model

start_date = date(2022, 5, 1)
end_date = date(2022, 5, 10)

result = A.objects\
    .annotate(date=Trunc('created', 'day', output_field=DateField())) \
    .filter(date__gte=start_date, date__lte=end_date) \
    .values('date')\
    .annotate(count=Count('id'))\
    .union(A.objects.extra(select={
             'date': 'unnest(Array[%s]::date[])' %
                     ','.join(map(lambda d: "'%s'::date" % d.strftime('%Y-%m-%d'),
                                  set(start_date + timedelta(n) for n in range((end_date - start_date).days + 1)) -
                                  set(A.objects.annotate(date=Trunc('created', 'day', output_field=DateField())) \
                                               .values_list('date', flat=True))))})\
            .annotate(count=Value(0))\
            .values('date', 'count'))\
    .order_by('date')

Brag

Answer 2

You can try this:

Define your date range

start_date = datetime(2018, 7, 22).date()
end_date = datetime(2018, 7, 25).date()

Annotate the queryset to count the occurrences of each date

annotated_queryset = A.objects.annotate(
    date=TruncDate('created')
).values('date').annotate(
    count=Count('id')
).order_by('date')

Generate a list of dates within the date range

date_objects = [start_date + timedelta(days=i) for i in range((end_date - start_date).days + 1)]

Create a dictionary mapping dates to counts from the annotated queryset

count_dict = {entry['date']: entry['count'] for entry in annotated_queryset}

Create a list of dictionaries containing all dates within the range, with their respective counts, filling in missing dates with a count of zero

result = [
    {'date': date.strftime('%Y-%m-%d'), 'count': count_dict.get(date, 0)}
    for date in date_objects
]

Discrete