使用datetime字段对Django模型条目进行逐日分组

I'm working with an Article like model that has a DateTimeField(auto_now_add=True) to capture the publication date (pub_date). This looks something like the following:

我正在使用一个类似model的文章，它有一个DateTimeField(auto_now_add=True)来捕获发布日期(pub_date)。这看起来如下所示:

class Article(models.Model):
    text = models.TextField()
    pub_date = models.DateTimeField(auto_now_add=True)

I want to do a query that counts how many article posts or entries have been added per day. In other words, I want to query the entries and group them by day (and eventually month, hour, second, etc.). This would look something like the following in the SQLite shell:

我想做一个查询，计算每天添加了多少文章或条目。换句话说，我想查询条目并按天(最后是月、小时、秒等)对它们进行分组。这看起来像下面的SQLite shell:

select pub_date, count(id) from "myapp_article"
where id = 1
group by strftime("%d", pub_date)
;

Which returns something like:

它返回类似:

2012-03-07 18:08:57.456761|5
2012-03-08 18:08:57.456761|9
2012-03-09 18:08:57.456761|1

I can't seem to figure out how to get that result from a Django QuerySet. I am aware of how to get a similar result using itertools.groupby, but that isn't possible in this situation (explanation to follow).

我似乎不知道如何从Django查询集获得结果。我知道如何使用itertools获得类似的结果。groupby，但是在这种情况下这是不可能的(解释如下)。

The end result of this query will be used in a graph showing the number of posts per day. I'm attempting to use the Django Chartit package to achieve this goal. Chartit puts a constraint on the data source (DataPool). The source must be a Model, Manager, or QuerySet, so using itertools.groupby is not an option as far as I can tell.

该查询的最终结果将用于显示每天的文章数量的图表。我正在尝试使用Django Chartit包来实现这个目标。Chartit对数据源(数据池)设置了一个约束。源必须是模型、管理器或QuerySet，所以要使用itertools。就我所知，groupby并不是一个选项。

So the question is... How do I group or aggregate the entries by day and end up with a QuerySet object?

问题是……我如何将这些条目分组或聚合，然后以一个QuerySet对象结束?

1 个解决方案

#1

Create an extra field that only store date data(not time) and annotate with Count:

创建一个只存储日期数据(而不是时间)的额外字段，并用Count进行注释:

Article.objects.extra({'published':"date(pub_date)"}).values('published').annotate(count=Count('id'))

Result will be:

结果将是:

published,count
2012-03-07,5
2012-03-08,9
2012-03-09,1

#1