Django count group by date from datetime

Question

I'm trying to count the dates users register from a DateTime field. In the database this is stored as '2016-10-31 20:49:38' but I'm only interested in the date '2016-10-31'.

The raw SQL query is:

select DATE(registered_at) registered_date,count(registered_at) from User 
where course='Course 1' group by registered_date;

It is possible using 'extra' but I've read this is deprecated and should not be done. It works like this though:

User.objects.all()
    .filter(course='Course 1')
    .extra(select={'registered_date': "DATE(registered_at)"})
    .values('registered_date')
    .annotate(**{'total': Count('registered_at')})

Is it possible to do without using extra?

I read that TruncDate can be used and I think this is the correct queryset however it does not work:

User.objects.all()
    .filter(course='Course 1')
    .annotate(registered_date=TruncDate('registered_at'))
    .values('registered_date')
    .annotate(**{'total': Count('registered_at')})

I get <QuerySet [{'total': 508346, 'registered_date': None}]> so there is something going wrong with TruncDate.

If anyone understands this better than me and can point me in the right direction that would be much appreciated.

Thanks for your help.

I would just use extra, this is exactly what it was made for. It's not deprecated yet, don't think it will be anytime soon, not without giving us some alternative solutions first, as currently it is the the only option for problems like this. So I wouldn't worry about it. — serg, Apr 05 '17 at 02:09

score 6 · Answer 1 · answered Jun 19 '17 at 14:57

I was trying to do something very similar and was having the same problems as you. I managed to get my problem working by adding in an order_by clause after applying the TruncDate annotation. So I imagine that this should work for you too:

User.objects.all()
    .filter(course='Course 1')
    .annotate(registered_date=TruncDate('registered_at'))
    .order_by('registered_date')
    .values('registered_date')
    .annotate(**{'total': Count('registered_at')})

Hope this helps?!

Siddharth Pant · Answer 2 · 2019-09-28T06:58:56.720

This is an alternative to using TruncDate by using `registered_at__date' and Django does the truncate for you.

from django.db.models import Count
from django.contrib.auth import get_user_model

metrics = {
    'total': Count('registered_at__date')
}
get_user_model().objects.all()
    .filter(course='Course 1')
    .values('registered_at__date')
    .annotate(**metrics)
    .order_by('registered_at__date')

For Postgresql this transforms to the DB query:

SELECT
    ("auth_user"."registered_at" AT TIME ZONE 'Asia/Kolkata')::date,
    COUNT("auth_user"."registered_at") AS "total"
FROM
    "auth_user"
GROUP BY
    ("auth_user"."registered_at" AT TIME ZONE 'Asia/Kolkata')::date
ORDER BY
    ("auth_user"."registered_at" AT TIME ZONE 'Asia/Kolkata')::date ASC;

From the above example you can see that Django ORM reverses SELECT and GROUP_BY arguments. In Django ORM .values() roughly controls the GROUP_BY argument while .annotate() controls the SELECT columns and what aggregations needs to be done. This feels a little odd but is simple when you get the hang of it.

How does it know you want to group by day as opposed to say year, month, hour or some other time interval? — AlxVallejo, Sep 06 '22 at 18:04
We are truncating timestamp into date which will be something like from "14/10/2022 17:37AM IST" to just "14/10/2022". So the group by runs on those date values which represent individual days. — Siddharth Pant, Oct 12 '22 at 12:08

Django count group by date from datetime

2 Answers2

Linked