Django count group by date from datetime

问题:

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.

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