django postgresql - CASE types interval and integer cannot be matched

Question

In Django I'm creating a queryset with CASE and annotation. It looks like this:

days_diff_calc = Case(
        When(Q(sent_date__isnull=True),
             then=None),
        When(Q(received_date__isnull=True),
             then=(timezone.now().date() - F('sent_date'))),
        default=(F('received_date') - F('sent_date')),
        output_field=DurationField()
    )

Item.objects.all().annotate(
        days_diff=days_diff_calc
    )

The important part of the query it creates looks something like this:

CASE 
                  WHEN "item"."sent_date" IS NULL THEN NULL 
                  WHEN "item"."received_date" IS NULL THEN ('2019-11-26' - "item"."sent_date") 
                  ELSE (interval '1 day'* ("item"."received_date" - "item"."sent_date"))
END AS "days_diff"

When running it in Postgres I get this error:

CASE types interval and integer cannot be matched
LINE 3: ...item"."received_date" IS NULL THEN ('2019-11-2...
                                               ^

It seems like for some reason, Django ORM adds the interval 1 day * after the ELSE, but doesn't do it after the second WHEN and I think that's the reason for this error. I don't understand - why does it add it only to one place and not to the other, and if I would want to add it to the other place (second WHEN) - I would I do it?

EDIT:

django version 2.1.5, psycopg2 version 2.6.2, Postgresql 9.6

all the 'date' fields are of type models.DateField(null=True, blank=True)

Tried also using ExpressionWrapper and got the same result:

days_diff_calc = Case(
        When(Q(sent_date__isnull=True),
             then=None),
        When(Q(received_date__isnull=True),
             then=ExpressionWrapper(timezone.now().date() - F('sent_date'),
                                    output_field=DurationField())),
        default=ExpressionWrapper(F('received_date') - F('sent_date'),
                                  output_field=DurationField()),
    )

I have so far been unable to replicate this. What versions of django and Postgres are you using? Also: can you show the relevant parts of your Item model? Meanwhile: you could try wrapping the problematic expression in an `ExpressionWrapper` call to force the `output_field` type. — RishiG, Nov 26 '19 at 23:36

score 1 · Answer 1 · answered Dec 01 '19 at 22:37

My understanding of this issue is not super deep, but I can at least offer the following workaround which seems to do the trick:

days_diff_calc = Case(
        When(Q(sent_date__isnull=True), then=None),
        When(Q(received_date__isnull=True),
             then=timedelta(days=1)*(timezone.now().date() - F('sent_date'))),
        default=(F('received_date') - F('sent_date')),
        output_field=DurationField()
    )

WRT why the unit conversion is not happening in your case, I'm not totally clear. In Postgres, subtracting dates yields an int, which is why it needs to be converted into a duration. I would guess that maybe on this line of expressions.py the date literal is not recognized as the same type as the DateField. If you run the same code on a model where sent_date is a DateTimeField, it seems to work ok, but in that case you don't need the extra interval expression.

I did what you suggested. Imported `from datetime import timedelta` and added `timedelta(days=1)*`. Now that part of the sql query is translated into `1 day, 0:00:00 *` and I get this error in postgresql - `syntax error at or near "day"` — user2880391, Dec 02 '19 at 17:56
Weird. Must be a version issue there. For me, it translates into `THEN ('1 days 0.000000 seconds'::interval * (...` I'm running with django 2.2.6 and Postgres 11.2, though. — RishiG, Dec 02 '19 at 20:09

django postgresql - CASE types interval and integer cannot be matched

1 Answers1