create a date difference generatedfield in model

Hi, I’m new to Python Django. I want to create a generatedfield that stores the # of days between today and another date field in my model. Currently I have the following:

class Orders(models.Model):
  Order_ID = models.CharField(max_length=10, primary_key=True)
  Order_Desc = models.CharField(max_length=100)
  Order_Type = models.CharField(max_length=100)
  Order_Status = models.CharField(max_length=50)
  Date_Received = models.DateField()
  Order_Owner = models.CharField(max_length=50)
  Days_In_Queue = models.GeneratedField(expression=now().date() - F('Date_Received'), 
    output_field=models.IntegerField(),
    db_persist=True,                                      
  ) 

However I’m getting the following when I tried to import data into sqlite db.

Error: unknown function:django_timestamp_diff()

Can someone please help with this? If there’s a better way, please share.

Thanks.

Welcome @mxi1982 !

Side note: I see that you enclosed your code between the three backticks, but didn’t post the code indented appropriately, and also enclosed the lines between single backticks. (I’ve taken the liberty of fixing the post.)

This expression: expression=now().date() - F('Date_Received') is not going to work the way you think it’s going to work. Since “now” is a Django function and not a database function, the now().date() function call will be initialized when the model is imported when the system is started. It is not going to be evaluated when an instance is saved.

And, even if you use the right function for the database, I still don’t think this is going to work as you are wanting it to. A persisted field is stored in the database, it isn’t calculated on an as-needed basis. Once it is saved, that’s the value it’s going to be.

I believe the appropriate solution would be for you to annotate the value when you are writing your query.

Hi Ken,
Thank you. A follow up question…I want to query the Orders table by the Days_In_Queue field, how would I do that?

Thanks.

You can use functions such as filter or order_by on an annotated field.

Hi Ken,

Following you advice…in my queryset I want to first calculate the date diff between today and a date field in the db, then based on the result classify the orders in queue into different buckets (e.g., ‘< 1 week’, ‘1-2 weeks’).
I know Django doesn’t allow <> in the queryset and you have to use __lt, __gt. But how would that work with a calculation such as ExpressionWrapper(datetime.date.today()-F(‘Date_Received’), output_field=fields.DateField())?

Thanks.

There are a couple different ways of doing this, depending upon what you’re going to do with this information after you’ve gathered it.

How many “buckets” do you need to create?
Is it limited to some specific number, or are you going to create as many as needed?
Is every bucket going to be the same size?
What are you going to do with these buckets after identifying their members?

Hi Ken,
I want to have 4 buckets: “< 1 week”, “1-2 weeks”, “2-3 weeks”, “> 3 weeks”. Each bucket may have different number of orders depending on the data.

I want to do a group by query based on these buckets and create charts using the query result.

Thanks

You can use the Case / When functions in an annotate to assign the row into a “bucket”, then you can either aggregate this data by bucket in a query, or do it in Python with the query results - it all depends upon what you’re trying to do with it.

Thanks Ken. I am determining which bucket each order belongs to by calculating the number of days the order has been in queue. Currently I have expressionwrapper(datetime.date.today()-F(‘Date_Received’)) to get the # of days the order is in queue but the challenge is I can’t use <= in the queryset code and __lte is not working either.

Please be more specific with what you have tried, and show the results you’re getting (error messages, etc)

Hi Ken,

Here’s what I’m trying to do:

Order_Age = Orders.objects.annotate(Aging=Case(When(ExpressionWrapper(datetime.date.today()-F(‘Date_Reported’), output_field=fields.DateField())<7, then=Value(‘< 1 week’)),
When(ExpressionWrapper(datetime.date.today()-F(‘Date_Reported’), output_field=fields.DateField())>=7, ExpressionWrapper(datetime.date.today()-F(‘Date_Reported’)<14, output_field=fields.DateField()), then=Value(‘1-2 weeks’)),
When(ExpressionWrapper(datetime.date.today()-F(‘Date_Reported’), output_field=fields.DateField())>=14, ExpressionWrapper(datetime.date.today()-F(‘Date_Reported’), output_field=fields.DateField())<21, then=Value(‘2-3 weeks’)),
When(ExpressionWrapper(datetime.date.today()-F(‘Date_Reported’), output_field=fields.DateField())>=21, then=Value(‘> 3 weeks’)),
output_field=fields.CharField(max_length=50)
)).values(‘Order_Age’).annotate(Order_total=Count(‘Order_ID’))

I get the following error:
‘<’ not supported between instances of ‘ExpressionWrapper’ and ‘int’.

I’m not sure what to use in place of ‘<’. I have seen examples of __lte but that doesn’t work either.

You’ll want to reorganize these expressions to remove the mathematical operations so that your When expressions are based on the date_reported field.
I’d also suggest breaking this out some to make it readable.

Kinda winging this - but I think this should be close:

from datetime import timedelta
from django.utils import timezone

today = timezone.now().date()
one_week = today - timedelta(days=7)
two_weeks = today - timedelta(days=14)
three_weeks = today - timedelta(days=21)

order_age = Orders.objects.annotate(
  aging=Case(
    When(date_reported__gt=one_week, then=Value(0)),
    When(date_reported__gt=two_weeks, then=Value(1)),
    When(date_reported__gt=three_weeks, then=Value(2)),
    default=Value(3)
  )
).order_by('aging')

From there, you can do whatever you want with that queryset.