CODEWITHSUNDEEP
pythondjangodjango-orm
A.J.
A.J.

Reputation: 9025

Django ORM calculate number of days between two date attributes

Scenario

I have a table student. it has following attributes

name,

age,

school_passout_date,

college_start_date

I need a report to know what is the avg number of days student get free between the passing the school and starting college.

Current approach

Currently i am irritating over the range of values finding days for each student and getting its avg.

Problem

That is highly inefficient when the record set gets bigger.

Question

Is there any ability in the Django ORM that gives me totals days between the two dates?

Possibility

I am looking for something like this. 

Students.objects.filter(school_passed=True, started_college=True).annotate(total_days_between=Count('school_passout_date', 'college_start_date'), Avg_days=Avg('school_passout_date', 'college_start_date'))

Upvotes: 10

Views: 4890

Answers (4)

Aidan
Aidan

Reputation: 4250

You can do this like so:

Model.objects.annotate(age=Cast(ExtractDay(TruncDate(Now()) - TruncDate(F('created'))), IntegerField()))

This lets you work with the integer value, eg you could then do something like this:

from django.db.models import IntegerField, F
from django.db.models.functions import Cast, ExtractDay, TruncDate

qs = (
    Model
    .objects
    .annotate(age=Cast(ExtractDay(TruncDate(Now()) - TruncDate(F('created'))), IntegerField()))
    .annotate(age_bucket=Case(
        When(age__lt=30, then=Value('new')),
        When(age__lt=60, then=Value('current')),
        default=Value('aged'),
        output_field=CharField(),
    ))
)

Upvotes: 8

Ersel Aker
Ersel Aker

Reputation: 855

This question is very old but Django ORM is much more advanced now.

It's possible to do this using F() functions.

from django.db.models import Avg, F
college_students = Students.objects.filter(school_passed=True, started_college=True)
duration = college_students.annotate(avg_no_of_days=Avg( F('college_start_date') - F('school_passout_date') )

Upvotes: 4

Spoutnik16
Spoutnik16

Reputation: 864

Mathematically, according to the (expected) fact that the pass out date is allway later than the start date, you can just get an average off all your start date, and all your pass out date, and make the difference.

This gives you a solution like that one

from django.db.models import Avg
avg_start_date = Students.objects.filter(school_passed=True, started_college=True).aggregate(Avg('school_start_date'))
avg_passout_date = Students.objects.filter(school_passed=True, started_college=True).aggregate(Avg('school_passout_date'))
avg_time_at_college = avg_passout_date - avg_start_date

Upvotes: 2

Spoutnik16
Spoutnik16

Reputation: 864

Django currently only accept aggregation for 4 function : Max, Min, Count, et Average, so this is a little tricky to do.

Then the solution is using the method extra . That way:

Students.objects.
    extra(select={'difference': 'school_passout_date' - 'college_start_date'}).
    filter('school_passed=True, started_college=True)

But then, you still have to do the average on the server side

Upvotes: 1

Related Questions