Django - 从时间戳获取不同的日期 [英] Django - get distinct dates from timestamp
问题描述
我试图按日期过滤用户,但直到找到数据库中用户的第一个和最后一个日期为止。虽然我可以让脚本过滤掉dups,但我想从一开始就使用Django的不同的
,因为它显着降低了。我试过
I'm trying to filter users by date, but can't until I can find the first and last date of users in the db. While I can have my script filter out dups later on, I want to do it from the outset using Django's distinct
since it significantly reduces. I tried
User.objects.values('install_time').distinct().order_by()
但由于 install_time
是一个 timestamp
,它包括日期AND时间(我不太在乎)。因此,仅筛选出来的是日期,我们可以检索多个用户的安装日期,而不是。
任何想法如何去做这个?我正在使用Django 1.3.1,Postgres 9.0.5和最新版本的psycopg2来运行。
Any idea how to do this? I'm running this using Django 1.3.1, Postgres 9.0.5, and the latest version of psycopg2.
编辑:我忘了添加数据类型 install_time
:
I forgot to add the data type of install_time
:
install_time = models.DateTimeField()
编辑2:以下是Postgres shell的一些示例输出快速解释我想要的:
EDIT 2: Here's some sample output from the Postgres shell, along with a quick explanation of what I want:
2011-09-19 00:00:00
2011-09-11 00:00:00
2011-09-11 00:00:00 <--filtered out by distinct() (same date and time)
2011-10-13 06:38:37.576
2011-10-13 00:00:00 <--NOT filtered out by distinct() (same date but different time)
我知道 Manager.raw
,但宁可用户 django.db.connection.cursor
直接写入查询,因为 Manager.raw
返回一个 RawQuerySet
其中,IMO比仅仅手动编写SQL查询和迭代更糟糕
I am aware of Manager.raw
, but would rather user django.db.connection.cursor
to write the query directly since Manager.raw
returns a RawQuerySet
which, IMO, is worse than just writing the SQL query manually and iterating.
推荐答案
在更大的数据集上执行报表 itertools.group_by
可能是太慢了。在这些情况下,我使postgres处理分组:
When doing reports on larger datasets itertools.group_by
might be too slow. In those cases I make postgres handle the grouping:
truncate_date = connection.ops.date_trunc_sql('day','timestamp')
qs = qs.extra({'date':truncate_date})
return qs.values('date').annotate(Sum('amount')).order_by('date')
这篇关于Django - 从时间戳获取不同的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!