Django range

Summary: in this tutorial, you’ll learn how to use the Django range to check if a value is between two values.

A quick introduction to SQL BETWEEN operator

In SQL, you use the BETWEEN operator to check if a value is between two values:

field_name BETWEEN low_value AND high_valueCode language: Python (python)

It’s equivalent to the following:

field_name >= low_value AND field_name <= high_valueCode language: Python (python)

The BETWEEN operator returns true if the field_name is between low_value and high_value. Otherwise, it returns False.

Using Django range with numbers

Django’s equivalent of the BETWEEN operator is range:

Entity.objects.filter(field_name__range=(low_value,high_value))Code language: Python (python)

For example, you can find employees whose id is between 1 and 5 using the range like this:

>>> Employee.objects.filter(id__range=(1,5))Code language: Python (python)

Behind the scenes, Django executes the following query:

SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE "hr_employee"."id" BETWEEN 1 AND 5Code language: SQL (Structured Query Language) (sql)

Using Django range with dates

Besides numbers and strings, the range also works with dates. For example, the following return all job assignments starting from January 1, 2020, to March 31, 2020:

>>> Assignment.objects.filter(begin_date__range=(start_date,end_date))      
SELECT "hr_assignment"."id",
       "hr_assignment"."employee_id",
       "hr_assignment"."job_id",
       "hr_assignment"."begin_date",
       "hr_assignment"."end_date"
  FROM "hr_assignment"
 WHERE "hr_assignment"."begin_date" BETWEEN '2020-01-01'::date AND '2020-03-31'::dateCode language: SQL (Structured Query Language) (sql)

NOT BETWEEN

The NOT operator negates the BETWEEN operator:

field_name NOT BETWEEN (low_value, high_value)

In other words, the NOT BETWEEN returns true if a value is not in a range of values. It is equivalent to the following:

field_name < low_value OR field_value > high_valueCode language: Python (python)

In Django, you can use the Q object with the range to check if a value is not in a range:

Entity.objects.filter(~Q(field_name__range=(low_value,high_value)))Code language: Python (python)

For example, you can find employees with the id are not in the range (1,5):

>>> Employee.objects.filter(~Q(id__range=(1,5)))
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE NOT ("hr_employee"."id" BETWEEN 1 AND 5)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the Django range to check if a value is in a range of values.