Filter data based on intervals in Django

Question

I have following model:

class MyModel(models.Model):
    due = models.DateTimeField(null=True)
    max_days_before_due = models.IntegerField()

I would like to filter out instances or rows that are past this due, ie. when due minus max_days_before_due is in the past. I am using this query:

current_date = timezone.now()

MyModel.objects.annotate(
    counted_days=Case(
        When(
            due__isnull=False,
            then=ExtractDay(F('due')-current_date)
        ),
        default=0,
        output_field=IntegerField()
    )
).filter(
    max_days_before_due__lt=F('counted_days')
)

I am receiving this error:

django_1    |   File "/usr/local/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
django_1    |     return self.cursor.execute(sql, params)
django_1    | django.db.utils.ProgrammingError: function pg_catalog.timezone(unknown, interval) does not exist
django_1    | LINE 1: ... '2020-08-26T15:03:11.111165+00:00'::timestamptz) AT TIME ZO...
django_1    |                                                              ^
django_1    | HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

When I delete the subtration from the annotation, the error does not show, but I need to be able to count the timedelta. Otherwise if I set the due to, let's say, 2020-08-30 and current_date is 2020-08-26 it returns 30 in the counted_days field instead of 4.

I've been trying to use this question and these docs as reference. I am using PostgreSQL in version 10 and Django in version 1.11, Python 3.4.

EDIT

It seems that this task should be pretty straightforward using plain SQL:

postgres=# SELECT DATE '2020-08-26' + 10;
  ?column?
------------
 2020-09-05

Maybe there's an easier way of doing this.

`F('max_days_before_due')` will be an *integer* type, where as the `current_date` is a `datetime`, That could be the reason — JPG, Aug 26 '20 at 16:01
@ArakkalAbu Sorry, I've messed the variables in the question. I'm subtracting two dates. so this shouldn't be the problem. — gonczor, Aug 26 '20 at 16:05
I think, it is better to use [**`Now(...)`**](https://docs.djangoproject.com/en/3.1/ref/models/database-functions/#django.db.models.functions.Now) than `timezone.now()` — JPG, Aug 26 '20 at 16:20

gonczor · Accepted Answer · 2020-08-27T07:13:41.203

After a few more hours into debugging I managed to discover what was the issue and how to solve it, so here we go.

The field I kept in my database was a "timestamp with timezone".

I managed to reproduce the error in plain sql in postgres. When selecting a TIMESTAMP and subtracting an integer from it I got the same error as showed in my Django logs:

postgres=# SELECT TIMESTAMP '2020-08-26 01:01:00.123456-01:00'  + 10 > DATE '2020-09-01';
ERROR:  operator does not exist: timestamp without time zone + integer
LINE 1: ...ECT TIMESTAMP '2020-08-26 01:01:00.123456-01:00'  + 10 > DAT...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

As I've said in the update to my question, adding or subtracting integers from date objects is fairly easy:

postgres=# SELECT DATE '2020-08-26 01:01:00.123456-01:00'  + 10 > DATE '2020-09-01';
 ?column?
----------
 t
(1 row)

This suggested using a proper casting in the annotation:

MyModel.objects.annotate(due_date=Case(
    When(
        due__isnull=False,
        then=Cast('due', output_field=DateField()) - F('max_days_before_due')
    ),
    default=None,
    output_field=DateTimeField()
).filter(due_date__gt=current_date)

And viola:

pprint(my_model_query.values('due', 'max_days_before_due', 'due_date')))

Returns:

<MyModelQuerySet [{'due': datetime.datetime(2020, 8, 20, 9, 31, 25, tzinfo=<UTC>), 'max_days_before_due': 30, 'due_date': datetime.date(2020, 7, 21)}]>

Filter data based on intervals in Django

1 Answers1