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
– RishiG, Commented Nov 26, 2019 at 23:36

RishiG · Accepted Answer · 2019-12-01 22:37:11Z

1

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.

answered Dec 1, 2019 at 22:37

RishiG

2,8301 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2880391 Over a year ago

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"

RishiG Over a year ago

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.

Collectives™ on Stack Overflow

django postgresql - CASE types interval and integer cannot be matched

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related