Calculate number of days of periods in year using Django (queryset) - postgres

Question

I am trying to figure out how to calculation a total number of period in a year in the database. Take for example, calculating total leaves in a year.

I am using:

Django - 1.11.7
Postgres - 9.4

Here is my models.py:

class Leave(models.Model):
    leavedatefrom = models.DateField()
    leavedateto = models.DateField()

An Example to illustrate the issue:

leaves taken in 1 year (01/01/2019 - 31/12/2019)

record 1: (leavedatefrom)01/02/2019 - (leavedateto)05/02/2019 ===> 5 days
record 2: (leavedatefrom)10/05/2019 - (leavedateto)12/05/2019 ===> 3 days

Total days = 5 + 3 = 8 days

How can calculate in Django of the number of days of periods in the database in a year ? Basically I want to get 8 days in the example above.

Where do you want to save the result? Do you already have the records? — A. Sarid, May 21 '19 at 05:24
yes in the model - the fields: (leavedatefrom and leavedateto) — Axil, May 21 '19 at 05:27

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I think I'm bit late. You could get the sum of days in a single SQL query

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

Leave.objects.filter(...).annotate(
    diff_date_in_integer=ExpressionWrapper(F('leavedateto') - F('leavedatefrom'), output_field=IntegerField())
).annotate(
    diff_date_in_days=ExpressionWrapper(F('diff_date_in_integer') / (1000000 * 60 * 60 * 24), output_field=IntegerField())).aggregate(
    sum=Sum('diff_date_in_days'))

In the filter(...) clause, you can add the filtering conditions such as the datetime or the user specific data

Description

annotate the difference between leavedateto and leavedatefrom in "integer" format (in diff_date_in_integer annotated field )
Converted the diff_date_in_integer data to days
using aggregate() function and Sum() DB function, we findount the total days.

score 0 · Answer 2 · answered May 21 '19 at 05:40

The following is a naive approach to get the list of periods.

Fetch all the leave records from the system and for each record, find out the period of days.

leaves = Leave.objects.filter(leavedatefrom__gte=datetime.date(2019, 1, 1), leavedateto__lte=datetime.date(2019, 12, 31

for leave in leaves:
    days = (leave.leavedatefrom.date() - leave. leavedateto.date()).days()
    period_list.append(days)
total_leaves = sum(period_list)

A. Sarid · Accepted Answer · 2019-05-24T13:38:44.763

You can basically make a query to get all the relevant leaves in the year by using the gte and lte django lookups. For example:

import datetime
leaves = Leave.objects.filter(
    leavedatefrom__gte=datetime.date(2019, 1, 1),
    leavedateto__lte=datetime.date(2019, 12, 31)
)

And then go over all the leaves and calculate the days delta between them and sum it up into another variable:

total_days = 0
for leave in leaves:
    total_days += (leave.leavedateto - leave.leavedatefrom).days
print total_days

See How to calculate number of days between two given dates? for more details and options on calculating the date ranges.

EDIT: One optimization that can be made if you don't want to hit the DB each iteration is to use django values_list():

leaves = Leave.objects.filter(
    leavedatefrom__gte=datetime.date(2019, 1, 1),
    leavedateto__lte=datetime.date(2019, 12, 31)
).values_list('leavedatefrom', 'leavedateto')

total_days = 0
for leavedateto, leavedatefrom in leaves:
    total_days += (leavedateto - leavedatefrom).days
print total_days

Thanks @JPG - It's also possible to use `values_list` in the query and go over the values instead. — A. Sarid, May 21 '19 at 06:36

Calculate number of days of periods in year using Django (queryset) - postgres

3 Answers3

Description