Fixed #28103 -- Added quarter extract, truncation, and lookup.

Thanks Mariusz Felisiak, Tim Graham, and Adam Johnson for review.

5 files changed, 43 insertions, 7 deletions

diff --git a/django/db/backends/mysql/operations.py b/django/db/backends/mysql/operations.py
index c1d0451a54..584eb56e70 100644
--- a/django/db/backends/mysql/operations.py
+++ b/django/db/backends/mysql/operations.py
@@ -39,6 +39,10 @@ class DatabaseOperations(BaseDatabaseOperations):
         if lookup_type in fields:
             format_str = fields[lookup_type]
             return "CAST(DATE_FORMAT(%s, '%s') AS DATE)" % (field_name, format_str)
+        elif lookup_type == 'quarter':
+            return "MAKEDATE(YEAR(%s), 1) + INTERVAL QUARTER(%s) QUARTER - INTERVAL 1 QUARTER" % (
+                field_name, field_name
+            )
         else:
             return "DATE(%s)" % (field_name)
 
@@ -64,6 +68,12 @@ class DatabaseOperations(BaseDatabaseOperations):
         fields = ['year', 'month', 'day', 'hour', 'minute', 'second']
         format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s')  # Use double percents to escape.
         format_def = ('0000-', '01', '-01', ' 00:', '00', ':00')
+        if lookup_type == 'quarter':
+            return (
+                "CAST(DATE_FORMAT(MAKEDATE(YEAR({field_name}), 1) + "
+                "INTERVAL QUARTER({field_name}) QUARTER - " +
+                "INTERVAL 1 QUARTER, '%%Y-%%m-01 00:00:00') AS DATETIME)"
+            ).format(field_name=field_name)
         try:
             i = fields.index(lookup_type) + 1
         except ValueError:
diff --git a/django/db/backends/oracle/operations.py b/django/db/backends/oracle/operations.py
index 0ee0fbf0fe..17c3d93a5c 100644
--- a/django/db/backends/oracle/operations.py
+++ b/django/db/backends/oracle/operations.py
@@ -67,6 +67,8 @@ END;
         elif lookup_type == 'week':
             # IW = ISO week number
             return "TO_CHAR(%s, 'IW')" % field_name
+        elif lookup_type == 'quarter':
+            return "TO_CHAR(%s, 'Q')" % field_name
         else:
             # https://docs.oracle.com/database/121/SQLRF/functions067.htm#SQLRF00639
             return "EXTRACT(%s FROM %s)" % (lookup_type.upper(), field_name)
@@ -81,6 +83,8 @@ END;
         # https://docs.oracle.com/database/121/SQLRF/functions271.htm#SQLRF52058
         if lookup_type in ('year', 'month'):
             return "TRUNC(%s, '%s')" % (field_name, lookup_type.upper())
+        elif lookup_type == 'quarter':
+            return "TRUNC(%s, 'Q')" % field_name
         else:
             return "TRUNC(%s)" % field_name
 
@@ -117,6 +121,8 @@ END;
         # https://docs.oracle.com/database/121/SQLRF/functions271.htm#SQLRF52058
         if lookup_type in ('year', 'month'):
             sql = "TRUNC(%s, '%s')" % (field_name, lookup_type.upper())
+        elif lookup_type == 'quarter':
+            sql = "TRUNC(%s, 'Q')" % field_name
         elif lookup_type == 'day':
             sql = "TRUNC(%s)" % field_name
         elif lookup_type == 'hour':
diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index 5892de92f8..faa8b16f1f 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -2,6 +2,7 @@
 SQLite3 backend for the sqlite3 module in the standard library.
 """
 import decimal
+import math
 import re
 import warnings
 from sqlite3 import dbapi2 as Database
@@ -309,6 +310,8 @@ def _sqlite_date_extract(lookup_type, dt):
         return (dt.isoweekday() % 7) + 1
     elif lookup_type == 'week':
         return dt.isocalendar()[1]
+    elif lookup_type == 'quarter':
+        return math.ceil(dt.month / 3)
     else:
         return getattr(dt, lookup_type)
 
@@ -320,6 +323,9 @@ def _sqlite_date_trunc(lookup_type, dt):
         return None
     if lookup_type == 'year':
         return "%i-01-01" % dt.year
+    elif lookup_type == 'quarter':
+        month_in_quarter = dt.month - (dt.month - 1) % 3
+        return '%i-%02i-01' % (dt.year, month_in_quarter)
     elif lookup_type == 'month':
         return "%i-%02i-01" % (dt.year, dt.month)
     elif lookup_type == 'day':
@@ -373,6 +379,8 @@ def _sqlite_datetime_extract(lookup_type, dt, tzname):
         return (dt.isoweekday() % 7) + 1
     elif lookup_type == 'week':
         return dt.isocalendar()[1]
+    elif lookup_type == 'quarter':
+        return math.ceil(dt.month / 3)
     else:
         return getattr(dt, lookup_type)
 
@@ -383,6 +391,9 @@ def _sqlite_datetime_trunc(lookup_type, dt, tzname):
         return None
     if lookup_type == 'year':
         return "%i-01-01 00:00:00" % dt.year
+    elif lookup_type == 'quarter':
+        month_in_quarter = dt.month - (dt.month - 1) % 3
+        return '%i-%02i-01 00:00:00' % (dt.year, month_in_quarter)
     elif lookup_type == 'month':
         return "%i-%02i-01 00:00:00" % (dt.year, dt.month)
     elif lookup_type == 'day':
diff --git a/django/db/models/functions/__init__.py b/django/db/models/functions/__init__.py
index b8bb89b171..f2e59f38ff 100644
--- a/django/db/models/functions/__init__.py
+++ b/django/db/models/functions/__init__.py
@@ -4,9 +4,9 @@ from .base import (
 )
 from .datetime import (
     Extract, ExtractDay, ExtractHour, ExtractMinute, ExtractMonth,
-    ExtractSecond, ExtractWeek, ExtractWeekDay, ExtractYear, Trunc, TruncDate,
-    TruncDay, TruncHour, TruncMinute, TruncMonth, TruncSecond, TruncTime,
-    TruncYear,
+    ExtractQuarter, ExtractSecond, ExtractWeek, ExtractWeekDay, ExtractYear,
+    Trunc, TruncDate, TruncDay, TruncHour, TruncMinute, TruncMonth,
+    TruncQuarter, TruncSecond, TruncTime, TruncYear,
 )
 
 __all__ = [
@@ -15,7 +15,7 @@ __all__ = [
     'Lower', 'Now', 'StrIndex', 'Substr', 'Upper',
     # datetime
     'Extract', 'ExtractDay', 'ExtractHour', 'ExtractMinute', 'ExtractMonth',
-    'ExtractSecond', 'ExtractWeek', 'ExtractWeekDay', 'ExtractYear',
-    'Trunc', 'TruncDate', 'TruncDay', 'TruncHour', 'TruncMinute', 'TruncMonth',
-    'TruncSecond', 'TruncTime', 'TruncYear',
+    'ExtractQuarter', 'ExtractSecond', 'ExtractWeek', 'ExtractWeekDay',
+    'ExtractYear', 'Trunc', 'TruncDate', 'TruncDay', 'TruncHour', 'TruncMinute',
+    'TruncMonth', 'TruncQuarter', 'TruncSecond', 'TruncTime', 'TruncYear',
 ]
diff --git a/django/db/models/functions/datetime.py b/django/db/models/functions/datetime.py
index debf65db3a..24f55e8f5b 100644
--- a/django/db/models/functions/datetime.py
+++ b/django/db/models/functions/datetime.py
@@ -101,6 +101,10 @@ class ExtractWeekDay(Extract):
     lookup_name = 'week_day'
 
 
+class ExtractQuarter(Extract):
+    lookup_name = 'quarter'
+
+
 class ExtractHour(Extract):
     lookup_name = 'hour'
 
@@ -118,6 +122,7 @@ DateField.register_lookup(ExtractMonth)
 DateField.register_lookup(ExtractDay)
 DateField.register_lookup(ExtractWeekDay)
 DateField.register_lookup(ExtractWeek)
+DateField.register_lookup(ExtractQuarter)
 
 TimeField.register_lookup(ExtractHour)
 TimeField.register_lookup(ExtractMinute)
@@ -179,7 +184,7 @@ class TruncBase(TimezoneMixin, Transform):
                 field.name, output_field.__class__.__name__ if explicit_output_field else 'DateTimeField'
             ))
         elif isinstance(field, TimeField) and (
-                isinstance(output_field, DateTimeField) or copy.kind in ('year', 'month', 'day', 'date')):
+                isinstance(output_field, DateTimeField) or copy.kind in ('year', 'quarter', 'month', 'day', 'date')):
             raise ValueError("Cannot truncate TimeField '%s' to %s. " % (
                 field.name, output_field.__class__.__name__ if explicit_output_field else 'DateTimeField'
             ))
@@ -214,6 +219,10 @@ class TruncYear(TruncBase):
     kind = 'year'
 
 
+class TruncQuarter(TruncBase):
+    kind = 'quarter'
+
+
 class TruncMonth(TruncBase):
     kind = 'month'