Fixed #30128 -- Fixed handling timedelta timezone in database functions.

6 files changed, 52 insertions, 4 deletions

diff --git a/django/db/backends/mysql/operations.py b/django/db/backends/mysql/operations.py
index 231665847a..ef908ad8ae 100644
--- a/django/db/backends/mysql/operations.py
+++ b/django/db/backends/mysql/operations.py
@@ -69,9 +69,20 @@ class DatabaseOperations(BaseDatabaseOperations):
         else:
             return "DATE(%s)" % (field_name)
 
+    def _prepare_tzname_delta(self, tzname):
+        if '+' in tzname:
+            return tzname[tzname.find('+'):]
+        elif '-' in tzname:
+            return tzname[tzname.find('-'):]
+        return tzname
+
     def _convert_field_to_tz(self, field_name, tzname):
         if settings.USE_TZ and self.connection.timezone_name != tzname:
-            field_name = "CONVERT_TZ(%s, '%s', '%s')" % (field_name, self.connection.timezone_name, tzname)
+            field_name = "CONVERT_TZ(%s, '%s', '%s')" % (
+                field_name,
+                self.connection.timezone_name,
+                self._prepare_tzname_delta(tzname),
+            )
         return field_name
 
     def datetime_cast_date_sql(self, field_name, tzname):
diff --git a/django/db/backends/oracle/operations.py b/django/db/backends/oracle/operations.py
index 77d330c411..6669ca5beb 100644
--- a/django/db/backends/oracle/operations.py
+++ b/django/db/backends/oracle/operations.py
@@ -94,6 +94,13 @@ END;
     # This regexp matches all time zone names from the zoneinfo database.
     _tzname_re = re.compile(r'^[\w/:+-]+$')
 
+    def _prepare_tzname_delta(self, tzname):
+        if '+' in tzname:
+            return tzname[tzname.find('+'):]
+        elif '-' in tzname:
+            return tzname[tzname.find('-'):]
+        return tzname
+
     def _convert_field_to_tz(self, field_name, tzname):
         if not settings.USE_TZ:
             return field_name
@@ -106,7 +113,7 @@ END;
             return "CAST((FROM_TZ(%s, '%s') AT TIME ZONE '%s') AS TIMESTAMP)" % (
                 field_name,
                 self.connection.timezone_name,
-                tzname,
+                self._prepare_tzname_delta(tzname),
             )
         return field_name
 
diff --git a/django/db/backends/postgresql/operations.py b/django/db/backends/postgresql/operations.py
index 66e5482be6..c502760e93 100644
--- a/django/db/backends/postgresql/operations.py
+++ b/django/db/backends/postgresql/operations.py
@@ -40,9 +40,16 @@ class DatabaseOperations(BaseDatabaseOperations):
         # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
         return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
 
+    def _prepare_tzname_delta(self, tzname):
+        if '+' in tzname:
+            return tzname.replace('+', '-')
+        elif '-' in tzname:
+            return tzname.replace('-', '+')
+        return tzname
+
     def _convert_field_to_tz(self, field_name, tzname):
         if settings.USE_TZ:
-            field_name = "%s AT TIME ZONE '%s'" % (field_name, tzname)
+            field_name = "%s AT TIME ZONE '%s'" % (field_name, self._prepare_tzname_delta(tzname))
         return field_name
 
     def datetime_cast_date_sql(self, field_name, tzname):
diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index 24d07cc11a..f4184fce05 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -408,6 +408,14 @@ def _sqlite_datetime_parse(dt, tzname=None, conn_tzname=None):
     if conn_tzname:
         dt = dt.replace(tzinfo=pytz.timezone(conn_tzname))
     if tzname is not None and tzname != conn_tzname:
+        sign_index = tzname.find('+') + tzname.find('-') + 1
+        if sign_index > -1:
+            sign = tzname[sign_index]
+            tzname, offset = tzname.split(sign)
+            if offset:
+                hours, minutes = offset.split(':')
+                offset_delta = datetime.timedelta(hours=int(hours), minutes=int(minutes))
+                dt += offset_delta if sign == '+' else -offset_delta
         dt = timezone.localtime(dt, pytz.timezone(tzname))
     return dt
 
diff --git a/docs/releases/3.0.txt b/docs/releases/3.0.txt
index e58a18f9da..80c1d8904b 100644
--- a/docs/releases/3.0.txt
+++ b/docs/releases/3.0.txt
@@ -315,6 +315,13 @@ backends.
   ``can_return_ids_from_bulk_insert`` are renamed to
   ``can_return_columns_from_insert`` and ``can_return_rows_from_bulk_insert``.
 
+* Database functions now handle :class:`datetime.timezone` formats when created
+  using :class:`datetime.timedelta` instances (e.g.
+  ``timezone(timedelta(hours=5))``, which would output ``'UTC+05:00'``).
+  Third-party backends should handle this format when preparing
+  :class:`~django.db.models.DateTimeField` in ``datetime_cast_date_sql()``,
+  ``datetime_extract_sql()``, etc.
+
 :mod:`django.contrib.gis`
 -------------------------
 
diff --git a/tests/db_functions/datetime/test_extract_trunc.py b/tests/db_functions/datetime/test_extract_trunc.py
index 854959aca6..2b7ee5befd 100644
--- a/tests/db_functions/datetime/test_extract_trunc.py
+++ b/tests/db_functions/datetime/test_extract_trunc.py
@@ -1,4 +1,4 @@
-from datetime import datetime, timedelta
+from datetime import datetime, timedelta, timezone as datetime_timezone
 
 import pytz
 
@@ -988,6 +988,8 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
         end_datetime = timezone.make_aware(end_datetime, is_dst=False)
         self.create_model(start_datetime, end_datetime)
         melb = pytz.timezone('Australia/Melbourne')
+        delta_tzinfo_pos = datetime_timezone(timedelta(hours=5))
+        delta_tzinfo_neg = datetime_timezone(timedelta(hours=-5, minutes=17))
 
         qs = DTModel.objects.annotate(
             day=Extract('start_datetime', 'day'),
@@ -999,6 +1001,9 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
             quarter=ExtractQuarter('start_datetime', tzinfo=melb),
             hour=ExtractHour('start_datetime'),
             hour_melb=ExtractHour('start_datetime', tzinfo=melb),
+            hour_with_delta_pos=ExtractHour('start_datetime', tzinfo=delta_tzinfo_pos),
+            hour_with_delta_neg=ExtractHour('start_datetime', tzinfo=delta_tzinfo_neg),
+            minute_with_delta_neg=ExtractMinute('start_datetime', tzinfo=delta_tzinfo_neg),
         ).order_by('start_datetime')
 
         utc_model = qs.get()
@@ -1011,6 +1016,9 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
         self.assertEqual(utc_model.quarter, 2)
         self.assertEqual(utc_model.hour, 23)
         self.assertEqual(utc_model.hour_melb, 9)
+        self.assertEqual(utc_model.hour_with_delta_pos, 4)
+        self.assertEqual(utc_model.hour_with_delta_neg, 18)
+        self.assertEqual(utc_model.minute_with_delta_neg, 47)
 
         with timezone.override(melb):
             melb_model = qs.get()