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.

Answer 1

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?!

Answer 2

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.