Nishat Shama Nishat Shama - 3 months ago 19
SQL Question

Django Query : how do I find the maximum time duration from start and end time fields?

Here is my Django model:

class Shift(models.Model):
worker = models.OneToOneField('Worker',null=True)
date = models.DateField(null=True)
shiftTime = models.CharField(max_length=10, default="N/A")
timeIn = models.TimeField(null=True)
timeOut = models.TimeField(null=True)


I need to find a worker who spent the maximum amount of time in the office in a given date range. How do I calculate the time duration from timeIn and timeOut field in Django query?

Edit: I don't want to use another attribute duration because that seems redundant. Is there any other way to do it than using raw query or duration attribute?

Answer

Django 1.10 introduced the ability to natively do date/time diffs through the ORM. This query would get you the longest shift:

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

longest_shift = Shift.objects.annotate(shift_length=ExpressionWrapper(
                                           F('timeOut') - F('timeIn'),
                                           output_field=DurationField()))\.
                                           order_by('-shift_length').first()

You can add a filter for a specific date range as required by adding a filter() clause before annotate().

Comments