Django model - query with date arithmetic on model with multiple DateTimeField fields

Question

I am using Django 3.2

I have a model like this:

class FoodItem(models.Model):
    display_date = models.DateTimeField()
    expiry_date = models.DateTimeField()
    safe_to_eat_days_after_expiry = models.PositiveSmallIntegerField()
    # ...

I want to run a query to get all items that have expired, but are still safe to eat.

This is what I have so far:

criterion1 = Q(expiry_date__gt=date.today())
criterion2 = Q(???)  #  expiry_date +timedelta(safe_to_eat_days_after_expiry ) <= date.today()

FoodItem.objects.filter(criterion1 & criterion2)

How do I implement this?

Abdul Aziz Barkat · Accepted Answer · 2021-05-12T10:10:05.040

2

You will need to convert your safe_to_eat_days_after_expiry field into a DurationField and use either annotate or alias (New in Django 3.2, won't select the value but can be further used in filters) so that you can use this calculation in your query:

from django.db.models import DateTimeField, DurationField, ExpressionWrapper, F
from django.utils import timezone

FoodItem.objects.alias(
    safe_till=ExpressionWrapper(
        F("expiry_date")
        + ExpressionWrapper(
            F("safe_to_eat_days_after_expiry") * 24 * 60 * 60 * 1000000, # days to microseconds
            output_field=DurationField(),
        ),
        output_field=DateTimeField(),
    )
).filter(expiry_date__lte=timezone.now(), safe_till__gte=timezone.now())

Note: You initialize your field as PositiveSmallInteger perhaps this is just a typo while posting but it is actually PositiveSmallIntegerField. Also since this is a duration it should ideally be a DurationField. Next you are using a DateTimeField when perhaps you actually should be using a DateField instead.

edited May 12 '21 at 10:10

answered May 12 '21 at 09:21

Abdul Aziz Barkat

19,475
3
20
33

Yes, well spotted, the `PositiveSmallInteger` was a typo (corrected now). I haven't come accross `ExpressionWrappers` before, or alias - will look those up. – Homunculus Reticulli May 12 '21 at 09:54
1

@HomunculusReticulli `alias` is a new feature in Django 3.2 as I said in the answer, you can use `annotate` instead if you use an older version. The only difference is that `annotate` would select the value and you can access it on the objects whereas `alias` won't select the values but allow you to use them in filters, other annotations, etc. – Abdul Aziz Barkat May 12 '21 at 09:56
Could you please explain why you are multiplying the DurationField Value by 1000000 - is the unit in milliseconds? – Homunculus Reticulli May 12 '21 at 10:09
@HomunculusReticulli that is because the unit is in microseconds, I have added a comment in the code. :) – Abdul Aziz Barkat May 12 '21 at 10:10
@HomunculusReticulli also had made a minor mistake in the logic changed `safe_till__lte` to `safe_till__gte`. – Abdul Aziz Barkat May 12 '21 at 10:14

Kushim · Answer 2 · 2021-05-11T22:50:34.797

0

Reading the docs I found that you could do a raw sql, so i guess something like this might work, just the myapp part change it for the name of your app.

FoodItem.objects.raw('''SELECT *
                       FROM myapp_fooditem
                       WHERE display_date>=DATEADD(day, expiry_date, display_date)''')

Ill recommend adding the field to the model and modifying your save method to be saved by default:

FoodItem(models.Model):
    ....
    safe_to_eat = models.DateTimeField()



def save(self, *args, **kwargs):
    if not self.safe_to_eat:
        self.safe_to_eat= self.expiry_date + datetime.timedelta(days=self.safe_to_eat_days_after_expiry)
    super(FoodItem, self).save(*args, **kwargs)

edited May 11 '21 at 22:50

answered May 11 '21 at 22:29

Kushim

303
1
7

Hmm, I'd rather not use Raw SQL if I can help it. – Homunculus Reticulli May 11 '21 at 22:30
By the way im suggesting that because of the timedelta piece, i referred to this original answer: https://stackoverflow.com/questions/11835870/sum-of-f-expression-and-timedelta-created-from-f-expression – Kushim May 11 '21 at 23:11
I've just seen your comment about modifying the model - unfortunately, I can't because it `FoodItem` is an abstract class that is used in a multiple inheritance relation - (and one of the child classes calls `save()` as well). – Homunculus Reticulli May 11 '21 at 23:50

score 0 · Answer 3 · answered Mar 07 '23 at 14:32

The accepted answer does not work for me on Django 3.2 + Postgres 12, failing with:

ProgrammingError: operator does not exist: timestamp with time zone + smallint
LINE 1: ...id") WHERE ("xxx"."created" + "xxx ...
                                       ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Upon inspecting the generated query, I noticed that Django does not convert the duration field to interval, but instead tries to perform a naive addition:

WHERE ("xxx"."created" + "xxx"."days_after") > 2023-03-07 14:27:47.332207+00:00

A simpler approach that works on Postgres is described in this answer:

now = timezone.now()

FoodItem.objects.filter(
    expiry_date__lte=now, 
    safe_till__gte=now + timezone.timedelta(days=1) * F('safe_to_eat_days_after_expiry')
)

Django model - query with date arithmetic on model with multiple DateTimeField fields

3 Answers3