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

Question

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.

Abscissa

I cannot reproduce your issue. Your query should work. I've tried the exact same query on a model of mine with a DateField, it returns "9661 days, 0:00:00" when I print the duration annotation. – Soucy

Can you please tell me your Django and MySql version. I have tried this on a different model as well as shown below, but still don't get the right duration values. – Abscissa

Django 1.11, postgreSQL 9.6 – Soucy

I understand you have different versions, but ExpressionWrapper was already supported in Django 1.8, not sure why it shouldn't work for you. But yeah, might be an issue with your specific setup. – Soucy

Also I can't propose an alternative query, all the alternatives I can think of are only supported in Django 1.11 or 2.2. But it's not clear whether this is a MySQL or Django issue. – Soucy

What I can suggest is: Try inspecting the raw SQL generated (print(qs.query)) to see if it's correct. Also as a last resort, write the SQL yourself, that way you can also see if your MySQL is producing strange results and use .raw(). – Soucy

printing the qs.query: SELECT `Portfolio_holding`.`id`, `Portfolio_holding`.`trans_date`, ... (2019-08-21 - `Portfolio_holding`.`trans_date`) AS `duration` FROM `Portfolio_holding` WHERE (`Portfolio_holding`.`id` = 1) – Abscissa

I would expect the date to be single-quoted: '2019-08-21'. Try to query directly your db in a mysql shell. – Soucy

mysql query without quotes +-----+------------+-----------+ | id | trans_date | duration | +-----+------------+-----------+ | 14 | 2016-04-14 | -20158424 | | 18 | 2016-05-02 | -20158512 | with quotes - '2019-08-21' +-----+------------+-----------+ | id | trans_date | duration | +-----+------------+-----------+ | 14 | 2016-04-14 | -20158395 | | 18 | 2016-05-02 | -20158483 | there is a change in duration values – Abscissa

Answer 1

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

Janeljanela