how can I group on converted values?

Question

so far I have this query:

q = Foobar.objects.values('updater','updated')
q = q.annotate(update_count=Count("id"))

which seems to generate a query like:

select updater, updated, count(id)
from foobar
group by updater, updated

"updated" is a date-time field, and I'd like to do my counts by day, with a query that looks like:

select updater, cast(updated as date), count(id)
from foobar
group by updater, cast(updated as date)

is there a way to do this with the Query API, or do I have to drop back to raw SQL?

Answer 1

QuerySet.dates() ( http://docs.djangoproject.com/en/dev/ref/models/querysets/#dates-field-kind-order-asc ) takes you part of the way there, but it doesn't seem to play nice with .values() and make a GROUP BY (I tried for a few minutes). Maybe you've already seen that anyway...

But it does show that Django already has an SQL function that you'll need if you write your own SQL version:

print(ProcessingState.objects.dates('timestamp', 'day').query)

yields

SELECT DISTINCT django_date_trunc("day", "databot_processingstate"."timestamp")
    FROM "databot_processingstate" ORDER BY 1 ASC

(sorry for the weird table names and stuff, it's just my own model I happend to have handy)

Answer 2

Django doesn't support this level of control over database queries - generally, you can't make queries use functions like CAST.

You have a few options in this case, though. First of all, most simply, you can just take the datetime object returned by the ORM object and remove the extra precision using datetime.replace().

Another option, if you know that you'll never want your Django app to use any precision in the updated field beyond the day, is to simply define updated in your models.py as a models.DateField() as opposed to models.DateTimeField(). This means data returned by the ORM Model will never have precision beyond the day.

Finally, I assume you're using the most recent Django (1.1), but in Django 1.2 (scheduled for May 10), you'll be able to do the following:

Foobar.objects.raw("select updater, cast(updated as date), count(id) from foobar group by updater, cast(updated as date)")

The result (assuming it has the same number of columns and column types as what you've defined in your Foobar model) will be a normal django ORM Queryset.