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

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.