CODEWITHSUNDEEP
djangodjango-models
Ben Kilah
Ben Kilah

Reputation: 3475

Django + PostgreSQL group by date on datetimefield

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?

Upvotes: 1

Views: 1095

Answers (2)

Antwan
Antwan

Reputation: 2125

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).

Upvotes: 1

Tommaso Barbugli
Tommaso Barbugli

Reputation: 12031

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')

Upvotes: 2

Related Questions