Group by Day in Django

Question

Does anyone there have an example of a "bat-ready" grouping by date? I tried all this here...

Subscription.objects.extra(select={'day': 'date(created_at)'}).values('day').annotate(available=Count('created_at'))

from django.db.models.aggregates import Count
Subscription.objects.extra({'date':"date(created_at)"}).values('date').annotate(count=Count('id'))



from django.db import connection
from django.db.models import Count

select = {'day': connection.ops.date_trunc_sql('day', 'created_at')}
Subscription.objects.extra(select=select).values('day').annotate(number=Count('id'))

But none returned what I want:

2016-01-22, 10
2016-01-21, 5
2016-01-15, 7

Answer 1

*To group per day, simply group by date.

Being of the type DateField, just do it this way:

>>> from django.db.models import Count
>>> Subscription.objects.values('created_at').annotate(number=Count('id'))
[{'number': 1, 'created_at': datetime.date(2015, 12, 5)}]

If the guy is DateTimeField, I converted to the type DateField using conditionals and functions:

>>> from django.db.models import DateField, Case, F
>>> Subscription.objects.annotate(tipo_datefield=Case(default=F('created_at'), output_field=DateField())).values('tipo_datefield').annotate(number=Count('id'))

Another way to accomplish this (not yet in the documentation) is by using Date to extract the day:

from django.db.models.expressions import Date
Subscription.objects.annotate(
    day=Date('created_at', 'day')
).values('day').annotate(number=Count('id'))

Answer 2

Django Aggregation, group by day

import datetime
import itertools
qs = Subscription.objects.values('created_at').values('created_at')
grouped = itertools.groupby(qs, lambda d: d.get('created_at').strftime('%Y-%m-%d'))
[(day, len(list(this_day))) for day, this_day in grouped]

Return

[('2016-01-24', 7),
 ('2016-01-22', 13),
 ('2016-01-21', 5),
 ('2016-01-15', 9),
 ('2016-01-14', 1),
 ('2016-01-09', 20)]

http://chase-seibert.github.io/blog/2012/02/24/django-aggregation-group-by-day.html