I have a django model with a DateTimeField.
class Point(models.Model):
somedata = models.CharField(max_length=256)
time = models.DateTimeField()
I want to get a count of the number of these objects for each day. I can do this with the following SQL query, but don't know how to do it through django.
SELECT DATE(`time`), Count(*)
FROM `app_point`
GROUP BY DATE(`time`)
Being able to restrict the results to a date range would also be good.
Try this,
from django.db.models.sql.aggregates import Aggregate
from django.db.models import Count
class Day(Aggregate):
"""Custom aggregator
"""
sql_function = 'DATE'
sql_template = "%(function)s(%(field)s)"
def __init__(self, lookup, **extra):
self.lookup = lookup
self.extra = extra
def _default_alias(self):
return '%s__%s' % (self.lookup, self.__class__.__name__.lower())
default_alias = property(_default_alias)
def add_to_query(self, query, alias, col, source, is_summary):
super(Day, self).__init__(col, source, is_summary, **self.extra)
query.aggregate_select[alias] = self
q = Point.objects.annotate(day=Day('time')).annotate(found=Count('time')).values('day', 'found')
# custom group by
q.query.group_by = ["day"]
print q
# should return something like
[{'found': 6, 'day': datetime.date(2010, 4, 30)},
{'found': 4, 'day': datetime.date(2010, 5, 1)},
{'found': 3, 'day': datetime.date(2010, 5, 2)}]
According to this post the above code would probably work only with MySql database.
