Add some date features to aggregation

[Adrian Ribao]

It would be nice to use datetime fields as a group in aggregation.

class Log(models.Model):
    datetime = models.DateTimeField(auto_now_add=True)
    subject = models.CharField(max_length=255)

Log.objects.values('datetime').annotate(num_logs=Count('id'))

Grouping by date(not datetime) is not possible.

[Erin Kelly]

Using the sqlite3 backend:

Log.objects.extra(select={'date': 'django_date_trunc("day", "testapp_log"."datetime")'}).values('date').annotate(num_logs=Count('id'))

Other backends will require replacing django_date_trunc() with the appropriate call. But I agree that there should be a single API for this.

[(none)]

Milestone post-1.0 deleted

[Russell Keith-Magee]

Bumping this back to accepted, but not on v1.1 path, because this isn't something that is strictly related to aggregates. You can already do a GROUP BY on any date/datetime field that exists on the model. This ticket is asking for the ability to do a GROUP BY on a value computed from another field on the model. This will first require the ability to compute fields and annotate them.

While this is definitely a good idea, it isn't a simple extension or omission from the v1.1 aggregates implementation. In the interim, it can be done using the approach Ian mentioned in the first comment.

[palewire]

I tried to replicate this on a model with a DateTimeField and ran into a problem I can't explain.
I'm working in Postgres and on the 1.1 official release.

The code was:

>>> from django.db.models import Count
>>> from app.models import Model
>>> Model.objects.extra(select={'year': "DATE_TRUNC('year', \"app_model\".\"datetime\")"}).values('year').annotate(total=Count('id'))

And the SQL I got back seemed to erroneously have the datefield included into the GROUP BY statement, like:

>>> from django.db import connection
>>> print connection.queries[-1]
{'time': '0.207', 'sql': 'SELECT (DATE_TRUNC(\'year\', "app_model"."datetime")) AS "year", COUNT("app_model"."id") AS total 
FROM "app_model" GROUP BY DATE_TRUNC(\'year\', "app_model"."datetime"), "app_model"."datetime" ORDER BY "app_model"."datetime" DESC LIMIT 21'}

[gmayer]

I ran into the same problem as palewire above but after some digging realised that my ordering in the model's Meta class was causing django to insert extra GROUP BY fields for all those listed in ordering. After commenting it out the extra().values().annotate() did the right thing, alternatively appending an order_by('year') to his command above would have worked too.

I'm not sure if this is a bug or a feature (why are ordering fields forced to be included in the GROUP BY SQL???) but if it's a feature then this gotcha is poorly documented. The ​queryset docs only mention something order_by() possibly conflicting with a values() under the distinct() heading, it should be a prominent warning under the values() and/or extra() description.

[kmpm]

I made a method something like this in a manager.
When I didn't have that last .order_by things went mad as for @gmayer but as soon as I added my own order I could put back the ordering in the model's Meta class.
Don't know if will help but it's a workaround if you need ordering in the Meta class.

def period_avrage(self, start_at=None, end_at=None, period_size='day'):

extra_psql={'period': "DATE_TRUNC('%s', \"collector_taglog\".\"created_at\")" % period_size}
qs = self.filter(data_good=True) #only good values
if start_at: qs = qs.filter(created_atgte=start_at)
if end_at: qs = qs.filter(created_atlte=end_at)
#do not remove the order by. Otherwise the ordering on the model will screw things up
return qs.extra(select=extra_psql).values('period').annotate(avg=Avg('v_num')).order_by('period')

[Raphael Michel]

Regression test

[Raphael Michel]

This has been fixed by the Expressions API and is therefore obsolete.