Django - Group By with Date part alone

Question

MyModel.objects.filter(created_at__gte='2011-03-09', created_at__lte='2011-03-11').values('created_at','status').annotate(status_count=Count('status'))

The above query has the problem with the created_at datetime field. Is it possible to tune the above query to ignore the time value and use the date value alone while performing group by?

Answer 1

Yes, that's possible using 'extra' and DB-level date formatting functions. Implementation can be found in e.g. django-qsstats-magic package.

Answer 2

You can do it easily using extra keyword in django.

MyModel.objects
.filter(created_at__gte='2011-03-09', created_at__lte='2011-03-11')
.extra({"created_at":"date_trunc('day', created_at)"}).values('created_at','status').annotate(status_count=Count('status'))

date_trunc is postgres function it takes, only precision specified.

Answer 3

I'm too late for this question, but for future reference, I want to point out that this is actually possible with native Django, as described at https://stackoverflow.com/a/2283913

Answer 4

I am not sure whether Django's ORM can perform a conversion of datetimes to dates in the middle of a query. You could, though, do the query first, get the results, then use the Python groupby() function to sort out the rows that are returned. Here is a small example of grouping datetimes by date:

from pprint import pprint
from datetime import datetime
from itertools import groupby

rows = [('Frodo party', datetime(3018, 9, 22, 10, 38)),
        ('Nazgul defeat Rangers', datetime(3018, 9, 22, 11, 57)),
        ('Frodo finishes packing', datetime(3018, 9, 23, 10, 59)),
        ('Gandalf tames Shadowfax', datetime(3018, 9, 23, 13, 11)),
        ('Gandalf crosses the Isen', datetime(3018, 9, 24, 18, 46))]

for key, values in groupby(rows, key=lambda row: row[1].date()):
    print('-')
    pprint(key)
    pprint(list(values))

As you can see, you have to provide groupby() with a key function that takes one of the objects that it is grouping and extracts the value by which the grouping should take place — in this case, it grabs the second item in each row with [1] and then calls the Python datetime method date() on it to extract the date part without the hours and minutes. The output of the script looks like this (the pprint() function is just a fancy "print" statement that indents the output, it won't be needed in your Django code!):

-
datetime.date(3018, 9, 22)
[('Frodo party', datetime.datetime(3018, 9, 22, 10, 38)),
 ('Nazgul defeat Rangers', datetime.datetime(3018, 9, 22, 11, 57))]
-
datetime.date(3018, 9, 23)
[('Frodo finishes packing', datetime.datetime(3018, 9, 23, 10, 59)),
 ('Gandalf tames Shadowfax', datetime.datetime(3018, 9, 23, 13, 11))]
-
datetime.date(3018, 9, 24)
[('Gandalf crosses the Isen', datetime.datetime(3018, 9, 24, 18, 46))]