How to calculate total working hours in Django?

Question

I created the following model:

class Timesheet(models.Model):
    date = models.DateField(auto_now=False, auto_now_add=False, verbose_name="Data")
    entry = models.TimeField(auto_now=False, auto_now_add=False, verbose_name="Hora Entrada")
    lunch = models.TimeField(auto_now=False, auto_now_add=False, null=True, blank=True, verbose_name="Início do Almoço")
    lunch_end = models.TimeField(auto_now=False, auto_now_add=False, null=True, blank=True, verbose_name="Fim do Almoço")
    out = models.TimeField(auto_now=False, auto_now_add=False, verbose_name="Hora de Saída")

This is then returned in a table, that has an extra field called "Total Hours", in which I need to calculate the total worked hours. Each entry refers to the same day.

And I have the following view:

def timesheet(request):
    c = Timesheet.objects.all()
    context = {'c': c}
    return render(request, "tracker/timesheet.html", context)

The calculation I need to do is: (out - entry) - (lunch_end - lunch).

How can I achieve this?

No, it really `(out - entry) - (lunch_end - lunch)`, as the lunch time should subtracted from the whole day of work. — overclock, Nov 06 '20 at 00:38

Willem Van Onsem · Accepted Answer · 2020-11-06T01:44:11.020

4

You can .annotate(…) [Django-doc] each Timesheet object with:

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

Timesheet.objects.annotate(
    total_time=ExpressionWrapper(
        ExpressionWrapper(F('out') - F('entry'), output_field=IntegerField()) -
        ExpressionWrapper(F('lunch_end') - F('lunch'), output_field=IntegerField()),
       output_field=DurationField()
    )
)

Here each Timesheet object that arises from this queryset will have an extra attribute .total_time which will contain the duration the person worked (so minus the luch break).

Or you can sum all records up with an .aggregate(…) [Django-doc]:

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

Timesheet.objects.aggregate(
    total_time=Sum(
        ExpressionWrapper(F('out') - F('entry'), output_field=IntegerField()) -
        ExpressionWrapper(F('lunch_end') - F('lunch'), output_field=IntegerField()),
       output_field=DurationField()
    )
)['total_time']

if the lunch and lunch_end can be None/NULL, you can work with Coalesce [Django-doc]:

from django.db.models import DurationField, ExpressionWrapper, F, IntegerField, Value
from django.db.models.functions import Coalesce

Timesheet.objects.aggregate(
    total_time=Sum(
        ExpressionWrapper(F('out') - F('entry'), output_field=IntegerField()) -
        Coalesce(ExpressionWrapper(F('lunch_end') - F('lunch'), output_field=IntegerField()), Value(0)),
       output_field=DurationField()
    )
)['total_time']

If you thus have .annotate(…)d all Timesheet records, and passed these as c, you can use:

{% for ci in c %}
    {{ ci.data }}: {{ ci.entry }} - {{ ci.out }}; {{ ci.total_time }}
{% endfor %}

edited Nov 06 '20 at 01:44

answered Nov 06 '20 at 00:48

Willem Van Onsem

443,496
30
428
555

Should I add this to my view, and then on the table use `{{c.total_time}}`? Or am I thinking wrong on the way this works? – overclock Nov 06 '20 at 01:02
@JoãodeSousa: you perform the query in the view. Then you can add the queryset (or the result) to the context, and render it in the template. – Willem Van Onsem Nov 06 '20 at 01:03
Oh wait! I see how this works! This is really great, and really simple when I come to think about it! Thank you so much! – overclock Nov 06 '20 at 01:04
I tried it and it works! But one problem arose, I didn't think before. It's possible for the lunch hours to be empty (e.g. only working afternoons), then the total time is displayed as None. Should I fix this with an if statement? – overclock Nov 06 '20 at 01:16
@JoãodeSousa: you can use `Coalesce` to use `0` instead of `NULL` in that case, see edit. – Willem Van Onsem Nov 06 '20 at 01:20
I get that `'datetime.timedelta' object is not iterable`. – overclock Nov 06 '20 at 01:26
@JoãodeSousa: well it is indeed not iterable, note that if you *aggregate*, then you get a single `timedelta` which is the total amount of all `Timesheet` records. If you annotate, it is a `.total_time` per `Timesheet` record. – Willem Van Onsem Nov 06 '20 at 01:31
How can then I loop for each `Timesheet` record the `.total_time`? I'm really lost here. – overclock Nov 06 '20 at 01:41
@JoãodeSousa: you `.annotate(..)` these, and iterate over the records in the template. – Willem Van Onsem Nov 06 '20 at 01:44

How to calculate total working hours in Django?

1 Answers1