[Django]-Django: Calculate difference between fields on rows from same model

In django-2.0 and higher, we can use .annotate(..) with an aggregate like Min, and a filter=... condition on that aggregate, like:

from django.db.models import F, Min, Q

t2=Min('time__time', filter=Q(time__timing_point=to_point))
t1=Min('time__time', filter=Q(time__timing_point=from_point))

Bib.objects.annotate(dtime=t2-t1)

We first introduce two variables:

1. t2 will contain the smallest time for a related Time object with timing_point the to_point (likely there is only one); and
2. t1 will contain the smallest time for a related Time object with timing_point the from_point.

We then make an annotation dtime with the difference between t2 and t1.

Since this is still part of the QuerySet, we can even order the Bibs on the dtime, etc.

Django will convert this to a query that looks like:

SELECT bib.*,
       (MIN(CASE WHEN time.timing_point_id = 2 THEN time.time ELSE NULL END) -
        MIN(CASE WHEN time.timing_point_id = 1 THEN time.time ELSE NULL END)) AS dtime
FROM bib
LEFT OUTER JOIN time ON bib.id = time.bib_id GROUP BY bib.id

With 2 and 1 in reality the primary keys of to_point and from_point respectively.

It might improve the efficiency of the query a bit further if you filter on the Timing model as well:

from django.db.models import F, Min, Q

t2=Min('time__time', filter=Q(time__timing_point=to_point))
t1=Min('time__time', filter=Q(time__timing_point=from_point))

Bib.objects.filter(
    time__timing_point__in=[from_point, to_point]
).annotate(dtime=t2-t1)

this will result in a query that looks like:

SELECT bib.*,
       (MIN(CASE WHEN time.timing_point_id = 2 THEN time.time ELSE NULL END) -
        MIN(CASE WHEN time.timing_point_id = 2 THEN time.time ELSE NULL END)) AS dtime
FROM bib LEFT OUTER JOIN time ON bib.id = time.bib_id
WHERE time.timing_point_id IN (1, 2)
GROUP BY bib.id