Django从日期时间开始按日期分组 [英] Django count group by date from datetime

问题描述

我正在尝试计算用户从DateTime字段中注册的日期。在数据库中，它存储为'2016-10-31 20:49:38'，但我只对日期'2016-10-31'感兴趣。

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

原始SQL查询为：

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?

我读到可以使用TruncDate，我认为这是正确的查询集，但是它不起作用：

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

我得到< QuerySet [{'total'：508346，'registered_date'：None}]]> ，因此TruncDate出了点问题。

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.

感谢您的帮助。

推荐答案

我正在尝试做非常相似的事情，和你有同样的问题。在应用 TruncDate 注释后，添加了 order_by 子句，成功解决了我的问题。因此，我想这也应该对您有用：

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

这篇关于Django从日期时间开始按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！