create a date difference generatedfield in model

Using Django Using the ORM
1

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.

2

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.

3

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.

4

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

5

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.

6

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?

7

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

8

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.

9

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.

10

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

11

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.

12

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.

14

Hi Ken,

I tried your suggested approach but am not getting the expected result. Currently, I have the following code:

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_Received__lt=one_week, then=Value(‘< 1 week’)),
When(Date_Received__gte=one_week, Date_Reported__lt=two_weeks, then=Value(‘1-2 weeks’)),
When(Date_Received__gte=two_weeks, Date_Reported__lt=three_weeks, then=Value(‘2-3 weeks’)),
When(Date_Received__gte=three_weeks, then=Value('>3 weeks)),)).values(‘Aging’).annotate(order_total=Count(‘Order_ID’))

However, I’m getting the following in the queryset:
Order_Age
<QuerySet [{‘Aging’: ‘< 1 week’, ‘order_total’: 33}, {‘Aging’: ‘>3 weeks’, ‘order_total’: 34}]>

I checked my data, there should be 22 orders between 1-2 weeks and 12 orders between 2-3 weeks. The value of the Date_Received field is in the “##/##/####” format. Should and how would I convert that field format to match the datetime.date format?

Thanks

15

It looks like you have your __gt and __lt comparisons reversed.

Also, when you’re posting code, please remember to enclose the code between lines of three backtick - ` characters.

16

Hi Ken,

Apologies for my novice knowledge with Django but not sure how I got the comparisons reversed. Llooking at the following two sample comparisons my understanding is that:

  • first one says if Date_Received is less than one_week, then i would classify this order as ‘< 1 week’
  • second one says if Date_Received is great than or equal to one_week and less than two_weeks, then i would classify this order as ‘1-2 weeks’.

When(Date_Received__lt=one_week, then=Value(‘< 1 week’)), When(Date_Received__gte=one_week, Date_Reported__lt=two_weeks, then=Value(‘1-2 weeks’)),
Is this not correct?

Thanks

17

No it is not correct.

You are creating dates in the past. You are creating ranges starting from the current date and working backwards. You are making a comparison with the earliest date in the range.

For example, today (as I write this) it is 24 Feb 2025.

The one_week variable is today - 7 days → 17 Feb 2025.

For something to have happened less than a week ago, the date of the event would need to be greater than 17 Feb. Things happening between 18 Feb and 24 Feb are the events that have happened less than a week ago.

If something happened on 14 Feb, that is more than a week ago, but less than 2 weeks ago (10 Feb).

18

Thanks for the clarification.

19

Hi Ken,

I have a follow up question related to date field in Django. I originally imported some test data (from Excel) into the backend sqlite db and i noticed the date value do not show up when I am looking at the field via the admin page. After some research, one Stack Overflow post suggested entering the date value via the admin page.

This will not be a feasible solution for me as going forward I will need to regularly import data into my Django app. Is there a more efficient way to ensure the date value from data sources are correctly loaded into the backend db?

Thanks

20

There isn’t enough detail here for me to comment on this. I’d need to know exactly how you’re loading the data and what the data is in the spreadsheets that are causing problems.

21

Hi Ken,

Below is a snippet of test data which is in CSV format.

image
image805×183 3.93 KB

I load this data into the backend sqlite db using the sqlite3 command:

.import Orders.csv Orders

When I open the Order table via Django admin page, the dates are empty so I couldn’t create charts from the data.

image
image540×348 8.94 KB

After some research, I fixed this issue by manually enter the date via the admin page for each order entry but I’m looking for a solution which will allow the dates to show up in admin page when I import the data from csv file.

Hope this additional info helps.