Django - Group By Date部分 [英] Django - Group By with Date part alone

问题描述

MyModel.objects.filter(created_at__gte='2011-03-09', created_at__lte='2011-03-11').values('created_at','status').annotate(status_count=Count('status'))

上述查询与 created_at datetime字段。

The above query has the problem with the created_at datetime field. Is it possible to tune the above query to ignore the time value and use the date value alone while performing group by?

推荐答案

我可以调整上述查询以忽略时间值并单独使用日期值我不知道Django的ORM是否可以执行数据时间转换为查询中间的日期。但是，您可以先查询查询结果，然后使用Python groupby（）函数来排序返回的行。以下是按日期分组数据时间的小例子：

I am not sure whether Django's ORM can perform a conversion of datetimes to dates in the middle of a query. You could, though, do the query first, get the results, then use the Python groupby() function to sort out the rows that are returned. Here is a small example of grouping datetimes by date:

from pprint import pprint
from datetime import datetime
from itertools import groupby

rows = [('Frodo party', datetime(3018, 9, 22, 10, 38)),
        ('Nazgul defeat Rangers', datetime(3018, 9, 22, 11, 57)),
        ('Frodo finishes packing', datetime(3018, 9, 23, 10, 59)),
        ('Gandalf tames Shadowfax', datetime(3018, 9, 23, 13, 11)),
        ('Gandalf crosses the Isen', datetime(3018, 9, 24, 18, 46))]

for key, values in groupby(rows, key=lambda row: row[1].date()):
    print('-')
    pprint(key)
    pprint(list(values))

如您所见，您必须提供 groupby（）带有一个键函数，它使用它所分组的对象之一，并提取应该进行分组的值 - 在这种情况下，它抓住第二个项在每一行使用 [1] 然后调用Python datetime 方法 date（）在其上提取日期部分没有时间和分钟。脚本的输出看起来像这样（ pprint（）函数只是一个想要输出的花哨的打印语句，在Django代码中不需要！）：

As you can see, you have to provide groupby() with a key function that takes one of the objects that it is grouping and extracts the value by which the grouping should take place — in this case, it grabs the second item in each row with [1] and then calls the Python datetime method date() on it to extract the date part without the hours and minutes. The output of the script looks like this (the pprint() function is just a fancy "print" statement that indents the output, it won't be needed in your Django code!):

-
datetime.date(3018, 9, 22)
[('Frodo party', datetime.datetime(3018, 9, 22, 10, 38)),
 ('Nazgul defeat Rangers', datetime.datetime(3018, 9, 22, 11, 57))]
-
datetime.date(3018, 9, 23)
[('Frodo finishes packing', datetime.datetime(3018, 9, 23, 10, 59)),
 ('Gandalf tames Shadowfax', datetime.datetime(3018, 9, 23, 13, 11))]
-
datetime.date(3018, 9, 24)
[('Gandalf crosses the Isen', datetime.datetime(3018, 9, 24, 18, 46))]

这篇关于Django - Group By Date部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！