使用其datetime字段按日分组Django模型条目 [英] Grouping Django model entries by day using its datetime field

问题描述

我正在使用具有DateTimeField（auto_now_add = True）的文章类型来捕获发布日期（pub_date）。这看起来像下面这样：

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:

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

我想做一个查询计算每天添加多少条文章或条目。换句话说，我想查询条目，并按日（最后是月，小时，秒等）进行分组。这在SQLite shell中将如下所示：

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:

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

其中返回如下：

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

我似乎无法弄清楚如何从Django QuerySet获取该结果。我知道如何使用itertools.groupby 获取类似的结果，但是在这种情况下是不可能的（说明如下）。

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 Chartit 软件包来实现这一目标。 Chartit对数据源（DataPool）施加约束。源必须是Model，Manager或QuerySet，所以使用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.

所以问题是...

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

推荐答案

创建一个额外的字段，只存储日期数据（而不是时间）和注释与计数：

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

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

结果将是：

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

这篇关于使用其datetime字段按日分组Django模型条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！