CODEWITHSUNDEEP
djangodjango-querysetannotatedatefield
frustrated_user
frustrated_user

Reputation: 71

How to annotate a queryset with the difference between today & a DateField using ExpressionWrapper

I'm trying to do an annotation of the queryset based on a DateField as shown.

I am using Django version 1.8.12 and MYSQL version 5.6.40.

Tried following How to annotate a queryset with number of days since creation, but here its a DateTimeField. The comments below says "Changing it to Value(now.date(), DateField()) - F('creation_date__date'), doesn't work"

The Model code is shown below:

class Holding(models.Model):
    trans_date = models.DateField(_("Trans. Date"), null=False)
    ...

And the annotate query that gives the wrong duration is shown below:

today = timezone.now().date()
testqs = Holding.objects.filter(id=1)
myqs = testqs.annotate(duration = ExpressionWrapper( Value(today, DateField()) - F('trans_date'), output_field=DurationField()))

And when i try to print, all I get is None for the duration. Difference printed for reference.

for i in myqs:
    print i.duration, '-', today-i.trans_date

None - 1224 days, 0:00:00
None - 1206 days, 0:00:00
None - 1144 days, 0:00:00
None - 1051 days, 0:00:00
None - 1045 days, 0:00:00

I expect the duration to be a timedelta values with the difference between today and trans_date and not None.

Upvotes: 6

Views: 1693

Answers (1)

simonltwick
simonltwick

Reputation: 310

This works for me (Django 2.2.20 with SQLite):

import datetime as dt
from django import models
from django.db.models import F, ExpressionWrapper, fields
from django.db.models.functions import Now, TruncDate

class Test(models.Model):
    due_date=models.DateField(null=True, blank=True, default=dt.date.today)

due_in = ExpressionWrapper(
        F('due_date') - TruncDate(Now()),
        output_field=fields.DurationField())
qs = Test.objects.annotate(due_in=due_in)  # due_in will be dt.timedelta

In some databases with native DurationField support, I believe you can do the following to extract days as an int field, which would allow grouping etc, but this is not supported in SQLite.

due_in_days = ExtractDay(due_in)
qs = Test.objects.annotate(due_in_days=due_in_days)  # result is int

Upvotes: 4

Related Questions