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?

Answer 1

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
]