django: datediff sql queries?

Question

I'm trying to do the equivalent of the following SQL in Django:

SELECT * FROM applicant WHERE date_out - date_in >= 1 AND date_out - date_in <= 6

I can do this as a RAW sql query, but this is becoming frustrating in dealing with a RawQuerySet instead of a regular QuerySet object as I would like to be able to filter it later in the code.

Answer 1

I came across the issue of Django not natively supporting Datediff (and other database equivalents), and needed to use such a function many times for a particular project.

Upon further reading, it became clear that the implementation of calculating an interval from two dates differs widely between major database flavours. This is probably why it's not got a native abstraction function in Django yet. So I wrote my own Django ORM function for datediff:

See: mike-db-tools Github repository

You'll see the varying syntax between the database backends written in the docstrings for the respective databases. Datediff supports sqlite, MySQL / MariaDB, PostgreSQL and Oracle.

Usage (Django 1.8+):

from db_tools import Datediff

# Define a new dynamic fields to contain the calculated date difference
applicants = Applicant.objects.annotate(
    days_range=Datediff('date_out','date_in', interval='days'),
)

# Now you can use this dynamic field in your standard filter query
applicants = applicants.filter(days_range__gte=1, days_range__lte=6)

I'm really quite derpy when it comes to my code, so I encourage you to fork and improve.

Answer 2

You can use the extra() method and pass in a where keyword argument. The value of where should be a list that contains the SQL WHERE clause of the query above. I tested this with Postgresql 8.4 and this is what it looked like in my case:

q = Applicant.objects.extra(where = ["""date_part('day', age(date_out, date_in)) >= 1 and
      date_part('day', age(date_out, date_in)) <= 6"""])

This will return you a valid QuerySet instance.