1

I have a model which has a datetimefield that I'm trying to annotate on grouping by date.

Eg:

order_totals = Transfer.objects.filter(created__range=[datetime.datetime.combine(datetime.date.today(), datetime.time.min) + datetime.timedelta(days=-5), datetime.datetime.combine(datetime.date.today(), datetime.time.max)]).values('created').annotate(Count('id'))

The problem with the above is it groups by every second/millisecond of the datetime field rather then just the date.

How would I do this?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Ben Kilah
  • 3,445
  • 9
  • 44
  • 56

2 Answers2

2

You should be able to solve this by using QuerySet.extra and add a column to the query

eg.

qs.filter(...).extra(select={'created_date': 'created::date'}).values('created_date')
Tommaso Barbugli
  • 11,781
  • 2
  • 42
  • 41
1

Starting on Django 1.8, you can also use the new DateTime expression (weirdly it's is not documented in the built-in expressions sheet).

import pytz
from django.db.models.expressions import DateTime

qs.annotate(created_date=DateTime('created', 'day', pytz.UTC))

If you want to group by created_date, just chain another aggregating expression :

qs.annotate(created_date=DateTime('created', 'day', pytz.UTC)).values('created_date').annotate(number=Count('id'))

(Redundant values is needed to generate the appropriate GROUP BY. See aggregation topic in Django documentation).

Antwan
  • 2,091
  • 2
  • 21
  • 25