I have a model Run that contains start_time and end_time timestamps that signify the beginning of data measuring, and the end (we call that a "run").
class Run(models.Model):
start_time = models.DateTimeField(db_index=True)
end_time = models.DateTimeField()
Recently, a customer has started to perform new types of operations, and one of those requires them to know the duration of a run (easy enough to figure out), but also filtering a table based on that duration.
The filtering is the part I don't have. So trying to filter by duration (a field we don't have on the model), I came up with the following query:
from django.db.models import F
test_query = Run.objects.all().annotate(duration=F('end_time') - F('start_time'))
By using annotate, along with F operations, I am able to add a new temporary field to my query, called "duration". The new field is of type datetime.timedelta as it is the result of performing arithmetic on two datetime objects. That part works as expected.
The problem comes down to trying to filter by that newly generated annotation, by using __gte and __lte lookups:
from datetime import timedelta
from django.db.models import F
test_query = Run.objects.all().annotate(duration=F('end_time') - F('start_time')).filter(duration__gte=timedelta(seconds=50))
For simplicity's sake, let's assume I only want to get Runs that are 50 seconds or longer, as an example.
My work so far with timedelta has been straightforward enough. end_time - start_time = timedelta_datetime_span
Except for the Django filter, it doesn't seem to want to take that in as a parameter, throwing the following error:
Traceback (most recent call last):
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/core/handlers/exception.py", line 34, in inner
response = get_response(request)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/core/handlers/base.py", line 115, in _get_response
response = self.process_exception_by_middleware(e, request)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/core/handlers/base.py", line 113, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/usr/lib/python3.5/contextlib.py", line 30, in inner
return func(*args, **kwds)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/contrib/auth/decorators.py", line 21, in _wrapped_view
return view_func(request, *args, **kwargs)
File "/home/foo/Projects/project_foo/project_foo/comp_foo/foo/views.py", line 2790, in RunsJson
test = Run.objects.all().annotate(duration=F('end_time') - F('start_time')).filter(duration__gte=timedelta(seconds=50))
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/query.py", line 892, in filter
return self._filter_or_exclude(False, *args, **kwargs)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/query.py", line 910, in _filter_or_exclude
clone.query.add_q(Q(*args, **kwargs))
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/sql/query.py", line 1290, in add_q
clause, _ = self._add_q(q_object, self.used_aliases)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/sql/query.py", line 1318, in _add_q
split_subq=split_subq, simple_col=simple_col,
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/sql/query.py", line 1207, in build_filter
condition = self.build_lookup(lookups, reffed_expression, value)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/sql/query.py", line 1116, in build_lookup
lookup = lookup_class(lhs, rhs)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/lookups.py", line 20, in __init__
self.rhs = self.get_prep_lookup()
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/lookups.py", line 70, in get_prep_lookup
return self.lhs.output_field.get_prep_value(self.rhs)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/fields/__init__.py", line 1410, in get_prep_value
value = super().get_prep_value(value)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/fields/__init__.py", line 1270, in get_prep_value
return self.to_python(value)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/db/models/fields/__init__.py", line 1371, in to_python
parsed = parse_datetime(value)
File "/home/foo/.virtualenvs/project_foo/lib/python3.5/site-packages/django/utils/dateparse.py", line 106, in parse_datetime
match = datetime_re.match(value)
TypeError: expected string or bytes-like object
The concatenation of .filter() after the .annotate() should work on the new duration field, since (afaik) a __gte or __lte lookup works off of a datetime.timedelta object.
What am I doing wrong with the filtering here?
I have something like your problem in one of my task. I hope this work for you. You should use ExpressionWrapper expression like this:
from django.db.models import ExpressionWrapper, DurationField
Run.objects.all().annotate(duration=ExpressionWrapper(F('end_time') - F('start_time'), output_field=DurationField()))
With this solution you can use duration__gte or duration__lte.