Incorrect Day Difference Calculation in Django ORM - Getting Extremely Large Numbers

Using Django Using the ORM
1

Hi Guys,

I’m currently working on a Django project where I need to calculate the number of days between two dates in a model. However, I’m running into an issue where the calculated day difference returns an extremely large and incorrect value (e.g., 26179200000000 days).

I have a model with a DateTimeField:

image

class MyModel(models.Model):
    date_received = models.DateTimeField(null=True, blank=True)

I want to calculate the difference in days between date_received and the current date using Django ORM. Here’s the code snippet I’m using:

from django.db.models import F, ExpressionWrapper, IntegerField, Case, When
from django.db.models.functions import TruncDate, Now

days_in_storage = MyModel.objects.annotate(
    days_diff=ExpressionWrapper(
        TruncDate(Now()) - TruncDate(F('date_received')),
        output_field=IntegerField()
    )
)

Instead of getting a reasonable number of days (e.g., 1, 2, 100 days), I’m getting an enormous value like 26179200000000 days.

Has anyone encountered this issue before? Is there something I’m missing in the ORM query that could be causing this problem? How can I correctly calculate the day difference between two dates in Django ORM?

When I try this, I get this error.
Extract requires native DurationField database support.

days_in_storage = (ExtractDay(ExpressionWrapper(Now() - F('date_received'), output_field=DurationField())))
2

I haven’t managed to get a working solution in the time I have but in case it helps before someone else can help better, I think the value you’re getting is the time difference in seconds, not days.

1 Like
3

I’m curious - what database engine are you using?

This query (or at least, one substantially identical) works for me using a DurationField on PostgreSQL.

1 Like
4

Django version 4.1.3

I use PostgreSQL in a production environment, and SQLite3 in a development environment.

Using DurationField doesn’t work for me because the value I need to get has to be an Integer value. During the orm operation, I divide the number of days_in_storage by another Integer.

So I handled these operations with the model property instead of using the Orm query. The advantage of using orm was that it allowed the user to sort the table. Instead, I allowed the user to sort the date_received. There may be a way to go, but for now it has worked for me. :slight_smile:

    @property
    def days_in_storage(self):
        if self.date_received:
            return (datetime.now() - self.date_received).days
        return 0

Thank you very much for your answers.