5

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.

frustrated_user
  • 61
  • 4
  • 1
    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. – dirkgroten Aug 21 '19 at 10:09
  • 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. – frustrated_user Aug 22 '19 at 11:21
  • Django 1.11, postgreSQL 9.6 – dirkgroten Aug 22 '19 at 11:23
  • 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. – dirkgroten Aug 22 '19 at 11:24
  • 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. – dirkgroten Aug 22 '19 at 11:27
  • 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()`. – dirkgroten Aug 22 '19 at 11:35
  • 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)``` – frustrated_user Aug 22 '19 at 11:54
  • I would expect the date to be single-quoted: `'2019-08-21'`. Try to query directly your db in a mysql shell. – dirkgroten Aug 22 '19 at 12:11
  • 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 – frustrated_user Aug 22 '19 at 12:25

1 Answers1

4

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
simonltwick
  • 310
  • 2
  • 6