Django中DateTime字段之间的汇总差异 [英] Aggregate difference between DateTime fields in Django

问题描述

我有一个表，其中包含与时间段(特别是客户的工作时间)相关的一系列条目:

I have a table containing a series of entries which relate to time periods (specifically, time worked for a client):

task_time:
id     |    start_time    |    end_time       |    client (fk)
1        08/12/2011 14:48   08/12/2011 14:50     2

我正在尝试从我的Django应用程序汇总给定客户端工作的所有时间:

I am trying to aggregate all the time worked for a given client, from my Django app:

time_worked_aggregate = models.TaskTime.objects.\
                        filter(client = some_client_id).\
                        extra(select = {'elapsed': 'SUM(task_time.end_time - task_time.start_time)'}).\
                        values('elapsed')

if len(time_worked_aggregate) > 0:
    time_worked = time_worked_aggregate[0]['elapsed'].total_seconds()
else:
    time_worked = 0

这似乎很不雅致，但确实可行.或者至少我是这样想的:事实证明，它在PostgreSQL数据库上可以正常工作，但是当我移到SQLite时，一切都消亡了.

This seems inelegant, but it does work. Or at least so I thought: it turns out that it works fine on a PostgreSQL database, but when I move over to SQLite, everything dies.

一些挖掘表明，这样做的原因是DateTime不是SQLite中的一流数据.以下原始SQLite查询将完成我的工作:

A bit of digging suggests that the reason for this is that DateTimes aren't first-class data in SQLite. The following raw SQLite query will do my job:

SELECT SUM(strftime('%s', end_time) - strftime('%s', start_time)) FROM task_time WHERE ...;

我的问题如下:

• 上面的Python示例似乎是回旋处.我们可以更优雅地做到这一点吗?
• 更重要的是，在现阶段，我们能否以一种既适用于Postgres也适用于SQLite的方式来做到这一点?理想情况下，我不想写原始的SQL查询并打开恰好就位的数据库后端.在 general 中，Django非常擅长保护我们免受此侵害. Django是否对此操作有合理的抽象?如果没有，对我来说，在后端进行条件切换的明智方式是什么?

• The Python sample above seems roundabout. Can we do this more elegantly?
• More importantly at this stage, can we do it in a way that will work on both Postgres and SQLite? Ideally, I'd like not to be writing raw SQL queries and switching on the database backend that happens to be in place; in general, Django is extremely good at protecting us from this. Does Django have a reasonable abstraction for this operation? If not, what's a sensible way for me to do a conditional switch on the backend?

对于上下文，我应该提到数据集有成千上万的条目；以下是不实际的:

I should mention for context that the dataset is many thousands of entries; the following is not really practical:

sum([task_time.end_date - task_time.start_date for task_time in models.TaskTime.objects.filter(...)])

推荐答案

我认为自Django 1.8以来，我们可以做得更好:

I think since Django 1.8 we can do better:

我只想绘制带有注解的部分，其他带有聚合的部分应该很简单:

I would like just to draw the part with annotation, the further part with aggregation should be straightforward:

from django.db.models import F, Func
SomeModel.objects.annotate(
    duration = Func(F('end_date'), F('start_date'), function='age')
)

[此处有关postgres年龄函数的更多信息: http://www. postgresql.org/docs/8.4/static/functions-datetime.html ]

[more about postgres age function here: http://www.postgresql.org/docs/8.4/static/functions-datetime.html ]

SomeModel的每个实例都将用包含时差的duration字段注释，在python中它将是datetime.timedelta()对象[有关日期时间timedelta的更多信息，请参见:

each instance of SomeModel will be anotated with duration field containg time difference, which in python will be a datetime.timedelta() object [more about datetime timedelta here: https://docs.python.org/2/library/datetime.html#timedelta-objects ]

这篇关于Django中DateTime字段之间的汇总差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！