Fixed #29767 -- Made date-related casts work on SQLite

Thanks Rémy Hubscher for the report and Tim Graham and Simon Charette for the reviews.

4 files changed, 58 insertions, 2 deletions

diff --git a/django/db/backends/base/features.py b/django/db/backends/base/features.py
index 18a3350994..3dd55e80b9 100644
--- a/django/db/backends/base/features.py
+++ b/django/db/backends/base/features.py
@@ -240,6 +240,10 @@ class BaseDatabaseFeatures:
     # Does the backend support CAST with precision?
     supports_cast_with_precision = True
 
+    # How many second decimals does the database return when casting a value to
+    # a type with time?
+    time_cast_precision = 6
+
     # SQL to create a procedure for use by the Django test suite. The
     # functionality of the procedure isn't important.
     create_test_procedure_without_params_sql = None
diff --git a/django/db/backends/sqlite3/features.py b/django/db/backends/sqlite3/features.py
index 770b40a1ba..6cf23e08ce 100644
--- a/django/db/backends/sqlite3/features.py
+++ b/django/db/backends/sqlite3/features.py
@@ -27,6 +27,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     supports_temporal_subtraction = True
     ignores_table_name_case = True
     supports_cast_with_precision = False
+    time_cast_precision = 3
     uses_savepoints = True
     can_release_savepoints = True
 
diff --git a/django/db/models/functions/comparison.py b/django/db/models/functions/comparison.py
index aa733816ec..44f269369d 100644
--- a/django/db/models/functions/comparison.py
+++ b/django/db/models/functions/comparison.py
@@ -14,6 +14,20 @@ class Cast(Func):
         extra_context['db_type'] = self.output_field.cast_db_type(connection)
         return super().as_sql(compiler, connection, **extra_context)
 
+    def as_sqlite(self, compiler, connection, **extra_context):
+        db_type = self.output_field.db_type(connection)
+        if db_type in {'datetime', 'time'}:
+            # Use strftime as datetime/time don't keep fractional seconds.
+            template = 'strftime(%%s, %(expressions)s)'
+            sql, params = super().as_sql(compiler, connection, template=template, **extra_context)
+            format_string = '%H:%M:%f' if db_type == 'time' else '%Y-%m-%d %H:%M:%f'
+            params.insert(0, format_string)
+            return sql, params
+        elif db_type == 'date':
+            template = 'date(%(expressions)s)'
+            return super().as_sql(compiler, connection, template=template, **extra_context)
+        return self.as_sql(compiler, connection, **extra_context)
+
     def as_mysql(self, compiler, connection, **extra_context):
         # MySQL doesn't support explicit cast to float.
         template = '(%(expressions)s + 0.0)' if self.output_field.get_internal_type() == 'FloatField' else None
diff --git a/tests/db_functions/comparison/test_cast.py b/tests/db_functions/comparison/test_cast.py
index ffbb357835..dafe0fa9aa 100644
--- a/tests/db_functions/comparison/test_cast.py
+++ b/tests/db_functions/comparison/test_cast.py
@@ -10,7 +10,7 @@ from django.test import (
     TestCase, ignore_warnings, override_settings, skipUnlessDBFeature,
 )
 
-from ..models import Author
+from ..models import Author, DTModel, Fan
 
 
 class CastTests(TestCase):
@@ -51,6 +51,40 @@ class CastTests(TestCase):
                 numbers = Author.objects.annotate(cast_int=Cast('alias', field_class()))
                 self.assertEqual(numbers.get().cast_int, 1)
 
+    def test_cast_from_db_datetime_to_date(self):
+        dt_value = datetime.datetime(2018, 9, 28, 12, 42, 10, 234567)
+        DTModel.objects.create(start_datetime=dt_value)
+        dtm = DTModel.objects.annotate(
+            start_datetime_as_date=Cast('start_datetime', models.DateField())
+        ).first()
+        self.assertEqual(dtm.start_datetime_as_date, datetime.date(2018, 9, 28))
+
+    def test_cast_from_db_datetime_to_time(self):
+        dt_value = datetime.datetime(2018, 9, 28, 12, 42, 10, 234567)
+        DTModel.objects.create(start_datetime=dt_value)
+        dtm = DTModel.objects.annotate(
+            start_datetime_as_time=Cast('start_datetime', models.TimeField())
+        ).first()
+        rounded_ms = int(round(.234567, connection.features.time_cast_precision) * 10**6)
+        self.assertEqual(dtm.start_datetime_as_time, datetime.time(12, 42, 10, rounded_ms))
+
+    def test_cast_from_db_date_to_datetime(self):
+        dt_value = datetime.date(2018, 9, 28)
+        DTModel.objects.create(start_date=dt_value)
+        dtm = DTModel.objects.annotate(start_as_datetime=Cast('start_date', models.DateTimeField())).first()
+        self.assertEqual(dtm.start_as_datetime, datetime.datetime(2018, 9, 28, 0, 0, 0, 0))
+
+    def test_cast_from_db_datetime_to_date_group_by(self):
+        author = Author.objects.create(name='John Smith', age=45)
+        dt_value = datetime.datetime(2018, 9, 28, 12, 42, 10, 234567)
+        Fan.objects.create(name='Margaret', age=50, author=author, fan_since=dt_value)
+        fans = Fan.objects.values('author').annotate(
+            fan_for_day=Cast('fan_since', models.DateField()),
+            fans=models.Count('*')
+        ).values()
+        self.assertEqual(fans[0]['fan_for_day'], datetime.date(2018, 9, 28))
+        self.assertEqual(fans[0]['fans'], 1)
+
     def test_cast_from_python_to_date(self):
         today = datetime.date.today()
         dates = Author.objects.annotate(cast_date=Cast(today, models.DateField()))
@@ -59,7 +93,10 @@ class CastTests(TestCase):
     def test_cast_from_python_to_datetime(self):
         now = datetime.datetime.now()
         dates = Author.objects.annotate(cast_datetime=Cast(now, models.DateTimeField()))
-        self.assertEqual(dates.get().cast_datetime, now)
+        time_precision = datetime.timedelta(
+            microseconds=10**(6 - connection.features.time_cast_precision)
+        )
+        self.assertAlmostEqual(dates.get().cast_datetime, now, delta=time_precision)
 
     def test_cast_from_python(self):
         numbers = Author.objects.annotate(cast_float=Cast(decimal.Decimal(0.125), models.FloatField()))