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
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()))
ExpressionWrapper and go with simply Timer.objects.annotate(duration=F('end_time') - F('start_time')). In this case, the duration will contain the difference between end_time and start_time in microseconds.
ExpressionWrapper and DurationFieldQuerySet.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')
