Django Select Query Time Diff

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, Dif开发者_Go百科f(start_time, end_time) FROM myTable

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()))

QuerySet.extra() will allow you to specify arbitrary expressions for a column. Note that the result will be DB-dependent.

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')

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')