Djano中的日期时间字段之间的总体差异 [英] Aggregate difference between datetime fields in Djano

问题描述

我有一张表，其中包含一系列与时间段有关的条目（具体来说，为客户使用的时间）：

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.

有点挖掘这样做的原因是SQLite中的 DateTimes 不是一流的数据。以下原始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查询并切换恰好就绪的数据库后端;在一般中，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目前只支持Min，Max，Avg和Count的聚合，因此使用原始SQL是实现所需要的唯一方法。当您使用原始SQL时，数据库独立性在窗口之外，所以不幸的是，你没有运气。您必须检测数据库并适当地更改SQL。

Django currently only supports aggregates for Min, Max, Avg and Count, so using raw SQL is the only way to achieve what you want. When you use raw SQL, database-independence is out the window, so unfortunately, you're out of luck. You'll have to just detect the database and alter the SQL appropriately.

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