Filter in range of two date fields

user3408657 picture user3408657 · Jan 28, 2018 · Viewed 8.4k times · Source

I have something like this:

 class Model(models.Model):
      start = models.DateTimeField()
      end = models.DateTimeField()

and I want to get all models that follow the following constraints:

given two query parameters qstart, qend

  • model.end > qstart
  • model.start < q.end

which would give all Model objects that lie between in the range of the two dates. I saw you can do

 Model.objects.filter(date__range=[qstart, qend])

however, that operates on a single field, where as I need to operate on 2 separate fields.

Answer

Kevin L. picture Kevin L. · Jan 28, 2018

Adapted from Django docs

filter()

filter(**kwargs)

Returns a new QuerySet containing objects that match the given lookup parameters. Multiple parameters are joined via AND in the underlying SQL statement.

In your case:

Model.objects.filter(start__lte=qend, end__gte=qstart)

Chaining filters

The result of refining a QuerySet is itself a QuerySet, so it’s possible to chain refinements together. In your case, for example:

Model.objects.filter(
     end__gte=qstart
).filter(
     start__lte=qend
)

This takes the initial QuerySet of all entries in the database, adds a filter, then another filter. The final result is a QuerySet containing all entries with end > qstart and start < qend.

Q objects

If you need to execute more complex queries (for example, queries with OR statements), you can use Q objects.

Q() objects make it possible to define and reuse conditions. Q objects can be combined using the & and | operators. When an operator is used on two Q objects, it yields a new Q object.

In your case:

from django.db.models import Q

Model.objects.get(
    Q(end__gte=qstart) & Q(start__lte=qend)
)