Solutions for ticket #31506

[David Sanders]

Hi folks,

Ticket 31506 is an older ticket that I thought I'd look into – there are a couple of points of discussion I thought I'd run past folks before offering a PR.

To summarise the issue:

On Postgres when you add or subtract an interval with a date the result is a timestamp. The issue reports that if you have a queryset to calculate an interval, then using ExpressionWrapper to define the output_field essentially ignores the response type that the db returns.

Eg:

class StartModel(Model):
    start = DateField()

StartModel.objects.create(start=date(2022, 1, 1))
qs = StartModel.objects.annotate(
    end=ExpressionWrapper(F("start") + timedelta(days=1), output_field=DateField())
)
print(type(qs.first().end))
<class 'datetime.datetime'>

This raises a couple of questions:

1. Is this the responsibility of ExpressionWrapper (or the relevant db backend) to ensure the correct types are returned or is it the responsibility of the developer to use Cast() ?

The equivalent with Cast() returns the correct type:
qs = StartModel.objects.annotate(end=Cast(F("start") + timedelta(days=1), output_field=DateField()))
print(type(qs.first().end))
<class 'datetime.date'>

If so, should the ticket be marked as wontfix?

2. If it is Django's responsibility there are a couple of solutions that work (fixes failing test attached to the ticket):

a. Add a db converter:

diff --git a/django/db/backends/postgresql/operations.py b/django/db/backends/postgresql/operations.py
index 2303703ebc..e4158b8130 100644
--- a/django/db/backends/postgresql/operations.py
+++ b/django/db/backends/postgresql/operations.py
@@ -1,3 +1,4 @@
+from datetime import datetime
 from psycopg2.extras import Inet

 from django.conf import settings
@@ -354,3 +355,15 @@ class DatabaseOperations(BaseDatabaseOperations):
             update_fields,
             unique_fields,
         )
+
+    def get_db_converters(self, expression):
+        converters = super().get_db_converters(expression)
+        internal_type = expression.output_field.get_internal_type()
+        if internal_type == "DateField":
+            converters.append(self.convert_datefield_value)
+        return converters
+
+    def convert_datefield_value(self, value, expression, connection):
+        if type(value) == datetime:
+            value = value.date()
+        return value

b. Manually cast the expression in ExpressionWrapper
diff --git a/django/db/models/expressions.py b/django/db/models/expressions.py
index 5e3c7cab82..347c107936 100644
--- a/django/db/models/expressions.py
+++ b/django/db/models/expressions.py
@@ -1247,6 +1247,10 @@ class ExpressionWrapper(SQLiteNumericMixin, Expression):
     def as_sql(self, compiler, connection):
         return compiler.compile(self.expression)

+    def as_postgresql(self, compiler, connection):
+        sql, params = self.as_sql(compiler, connection)
+        return "{}::{}".format(sql, self.output_field.db_type(connection)), params
+
     def __repr__(self):
         return "{}({})".format(self.__class__.__name__, self.expression)

Any other ideas?

Regards,
David