Django 选择查询时间差异

发布于 2024-11-02 03:47:50 字数 209 浏览 1 评论 0原文

我正在尝试使用以下列等查询 django 中的数据库表：

id | start_time | end_time

我可以直接在查询中获取差异，而不是获取两者的单独值吗？有这样的效果：

SELECT id, Diff(start_time, end_time) FROM myTable

原文

I am trying to query a database table in django with, among others, the following columns:

id | start_time | end_time

Rather than getting the separate values for the two, can I just get the difference directly in a query?
Something to this effect:

SELECT id, Diff(start_time, end_time) FROM myTable

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

热风软妹 2024-11-09 03:47:51

QuerySet.extra()将允许您为列指定任意表达式。请注意，结果将取决于数据库。

回复收藏 0 原文

夜血缘 2024-11-09 03:47:51

在我看来，最简单的方法是添加第三列，并在每次修改对象时更新它，如下所示：

class Timer(models.Model)

    start_time = models.DateTimeField()
    end_time = models.DateTimeField()
    diff_time = models.DateTimeField()

    def __init__(self, *args, **kwargs):
        super(Timer, self).__init__(*args, **kwargs)
        self.diff_time = self.end_time - self.start_time

    def save(self, *args, **kwargs):
        self.diff_time = self.end_time - self.start_time
        super(Timer, self).save(*args, **kwargs)

我试图寻找带有注释的解决方案，但是 mysql 中都不支持聚合函数也不是 postgres。即使在那里，似乎很好地权衡了一个额外的列，而不必计算每个查询中每一行的差异！

哦，你可以像这样检索你想要的东西：

 Timer.objects.values('id', 'diff_time')

It seems to me that the easiest way is to add a third column with the difference and update it every time the object is modified like this:

class Timer(models.Model)

    start_time = models.DateTimeField()
    end_time = models.DateTimeField()
    diff_time = models.DateTimeField()

    def __init__(self, *args, **kwargs):
        super(Timer, self).__init__(*args, **kwargs)
        self.diff_time = self.end_time - self.start_time

    def save(self, *args, **kwargs):
        self.diff_time = self.end_time - self.start_time
        super(Timer, self).save(*args, **kwargs)

I tried to look for a solution with annotate, but there is no support for aggregate functions in either mysql nor postgres. Even if there where, seem like nice trade off an extra column for not having to calculate the diff for each row on each query!

Oh, and you can just retrieve what you want like this:

 Timer.objects.values('id', 'diff_time')

回复收藏 0 原文

烟若柳尘 2024-11-09 03:47:51

从 Django 1.8 开始，不鼓励使用 extra< /a> 支持注释+RawSQL。以下适用于 MySQL：

res = MyTable.objects.annotate(duration=RawSQL('datediff(endtime, starttime)', ()))
# examine the results
print res.values_list('duration', 'starttime', 'endtime')

As of Django 1.8, extra is discouraged in favor of annotate+RawSQL. The following works for MySQL:

res = MyTable.objects.annotate(duration=RawSQL('datediff(endtime, starttime)', ()))
# examine the results
print res.values_list('duration', 'starttime', 'endtime')

回复收藏 0 原文

思念满溢 2024-11-09 03:47:50

这可以完全通过 Django 1.10 及以上。使用这样的模型：

class Timer(models.Model)

    start_time = models.DateTimeField()
    end_time = models.DateTimeField()

您可以使用持续时间来注释对象，如下所示：

from django.db.models import DurationField, ExpressionWrapper, F

Timer.objects.annotate(duration=ExpressionWrapper(F('end_time') - F('start_time'),
                                                  output_field=DurationField()))

This can be done entirely through the ORM in Django 1.10 and above. With a model like so:

class Timer(models.Model)

    start_time = models.DateTimeField()
    end_time = models.DateTimeField()

You can annotate the objects with the duration like so:

from django.db.models import DurationField, ExpressionWrapper, F

Timer.objects.annotate(duration=ExpressionWrapper(F('end_time') - F('start_time'),
                                                  output_field=DurationField()))

回复收藏 0 原文

~没有更多了~