Count number of records by date in Django

Question

I have a model similar to the following:

class Review(models.Model):
    venue = models.ForeignKey(Venue, db_index=True)
    review = models.TextField()  
    datetime_created = models.DateTimeField(default=datetime.now)

I'd like to query the database to get the total number of reviews for a venue grouped by day. The MySQL query would be:

SELECT DATE(datetime_created), count(id) 
FROM REVIEW 
WHERE venue_id = 2
GROUP BY DATE(datetime_created);

What is the best way to accomplish this in Django? I could just use

Review.objects.filter(venue__pk=2)

and parse the results in the view, but that doesn't seem right to me.

Possible duplicate of [Django: Group by date (day, month, year)](http://stackoverflow.com/questions/8746014/django-group-by-date-day-month-year) — tback, Jun 24 '16 at 07:25

score 96 · Accepted Answer · edited Jun 13 '14 at 21:46

96

This should work (using the same MySQL specific function you used):

Review.objects.filter(venue__pk=2)
    .extra({'date_created' : "date(datetime_created)"})
    .values('date_created')
    .annotate(created_count=Count('id'))

edited Jun 13 '14 at 21:46

Denilson Sá Maia

47,466
33
109
111

answered Feb 17 '10 at 19:51

ara818

1,049
8
4

2

That's like my answer but better!! – Zach Feb 17 '10 at 19:54
Was sadly missing the .extra().. Very powerful .Thanks ! – Ramez Ashraf Jun 24 '14 at 01:11
Something I can't get it ,,, using .extra() BEFORE the .values() have a different result then using it AFTER the .values() clause ?!! Any explanation master ?! – Ramez Ashraf Jun 24 '14 at 01:35
I'm a little late here @radev but here's the answer you're looking for: "If you use a values() clause after an extra() call, any fields defined by a select argument in the extra() must be explicitly included in the values() call. Any extra() call made after a values() call will have its extra selected fields ignored." https://docs.djangoproject.com/en/1.7/ref/models/querysets/ – Ben Liyanage Dec 24 '14 at 20:37
2

Right, that's a result of somewhat mysterious behavior of the [values() clause](https://docs.djangoproject.com/en/dev/topics/db/aggregation/#values). **BUT** also keep in mind that using this in querysets with ordeing aplied (either through the _order_by()_ part of a queryset or the fields that are used in the default ordering on a model) **won't work until you** explicitly **clear** any ordering in the query -https://docs.djangoproject.com/en/dev/topics/db/aggregation/#interaction-with-default-ordering-or-order-by – Igor Gai Sep 09 '15 at 15:45
Nowadays, [`.extra` should only be used as a last resort](https://docs.djangoproject.com/en/1.10/ref/models/querysets/#django.db.models.query.QuerySet.extra). Is there any alternative? – mgalgs Jan 05 '17 at 00:33
How to get zero count if count is less than 1 . – Anish Menon Jul 23 '18 at 20:36
Bit late to the party, but you can bin the `extra` and do simply `.values('date_created__date')` to achieve the cast nowadays. See: https://docs.djangoproject.com/en/dev/ref/models/querysets/#date (there's also related ones to get `year`, `month`, etc.) – Harry Vane Oct 22 '19 at 14:02

Anthony Manning-Franklin · Answer 2 · 2020-07-16T12:58:14.777

27

Now that Extra() is being depreciated a more appropriate answer would use Trunc such as this accepted answer

Now the OP's question would be answered as follows

from django.db.models.functions import TruncDay

Review.objects.all()
    .annotate(date=TruncDay('datetime_created'))
    .values("date")
    .annotate(created_count=Count('id'))
    .order_by("-date")

edited Jul 16 '20 at 12:58

answered Jan 30 '17 at 07:34

Anthony Manning-Franklin

4,408
1
18
23

Missing parenthesis? `.annotate(date=TruncDay('datetime_created')` – AlvaroAV Jul 15 '20 at 10:17
1

Still works in 2021! – Sharpless512 Nov 06 '21 at 08:24
Just used this example in 2022! – Brylie Christopher Oxley Apr 04 '22 at 10:24

score 17 · Answer 3 · answered Dec 24 '15 at 19:25

17

Just for completeness, since extra() is aimed for deprecation, one could use this approach:

from django.db.models.expressions import DateTime

Review.objects.all().\
    annotate(month=DateTime("timestamp", "month", pytz.timezone("Etc/UTC"))).\
    values("month").\
    annotate(created_count=Count('id')).\
    order_by("-month")

It worked for me in django 1.8, both in sqlite and MySql databases.

answered Dec 24 '15 at 19:25

avikam

1,018
9
11

1

also you can use django timezone utils instead of pytz.timezone `django.utils.timezone.get_default_timezone` – cheap_grayhat Nov 07 '16 at 09:37
This seems to have been moved under [Extract](https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#django.db.models.functions.datetime.Extract) in Django 1.10 – Anthony Manning-Franklin Jan 30 '17 at 07:03

score 14 · Answer 4 · edited Jun 13 '14 at 21:47

14

If you were storing a date field, you could use this:

from django.db.models import Count

Review.objects.filter(venue__pk = 2)
    .values('date').annotate(event_count = Count('id'))

Because you're storing datetime, it's a little more complicated, but this should offer a good starting point. Check out the aggregation docs here.

edited Jun 13 '14 at 21:47

Denilson Sá Maia

47,466
33
109
111

answered Feb 17 '10 at 18:32

Zach

18,594
18
59
68

MadisonTrash · Answer 5 · 2016-04-11T05:56:25.037

3

Also you can define custom function:

from django.db.models.expressions import Func

# create custom sql function
class ExtractDateFunction(Func):
    function = "DATE" # thats the name of function, the way it mapped to sql

# pass this function to annotate
Review.objects.filter(venue__pk=2)
      .annotate(date_created=ExtractDateFunction("datetime_created"))
      .values('date_created')
      .annotate(created_count=Count('id'))

Just make sure that your DB engine supports DATE function

edited Apr 11 '16 at 05:56

answered Apr 11 '16 at 05:50

MadisonTrash

5,444
3
22
25

1

The `ExtractDateFunction` defined here won't support timezone modification as seen here: https://docs.djangoproject.com/en/1.11/ref/models/database-functions/ You can get the query to respect timezones, as of 1.10, by using `TruncDate` (see that link) instead. (As [here](https://stackoverflow.com/a/41930880/221533)) – freyley Sep 05 '18 at 04:18

Count number of records by date in Django

5 Answers5

Linked

Related