Fixed #33037 -- Fixed Trunc() with offset timezones on MySQL, SQLite, Oracle.

4 files changed, 36 insertions, 16 deletions

diff --git a/AUTHORS b/AUTHORS
index 26cfe71138..6fef75cf43 100644
--- a/AUTHORS
+++ b/AUTHORS
@@ -919,6 +919,7 @@ answer newbie questions, and generally made Django that much better:
     Sergey Fedoseev <fedoseev.sergey@gmail.com>
     Sergey Kolosov <m17.admin@gmail.com>
     Seth Hill <sethrh@gmail.com>
+    Shafiya Adzhani <adz.arsym@gmail.com>
     Shai Berger <shai@platonix.com>
     Shannon -jj Behrens <https://www.jjinux.com/>
     Shawn Milochik <shawn@milochik.com>
diff --git a/django/db/backends/sqlite3/_functions.py b/django/db/backends/sqlite3/_functions.py
index 0171b60f38..6d07d3d78b 100644
--- a/django/db/backends/sqlite3/_functions.py
+++ b/django/db/backends/sqlite3/_functions.py
@@ -118,7 +118,10 @@ def _sqlite_datetime_parse(dt, tzname=None, conn_tzname=None):
             hours, minutes = offset.split(":")
             offset_delta = timedelta(hours=int(hours), minutes=int(minutes))
             dt += offset_delta if sign == "+" else -offset_delta
-        dt = timezone.localtime(dt, zoneinfo.ZoneInfo(tzname))
+        # The tzname may originally be just the offset e.g. "+3:00",
+        # which becomes an empty string after splitting the sign and offset.
+        # In this case, use the conn_tzname as fallback.
+        dt = timezone.localtime(dt, zoneinfo.ZoneInfo(tzname or conn_tzname))
     return dt
 
 
diff --git a/django/db/backends/utils.py b/django/db/backends/utils.py
index f363253ca3..ab0ea8258b 100644
--- a/django/db/backends/utils.py
+++ b/django/db/backends/utils.py
@@ -200,6 +200,8 @@ def split_tzname_delta(tzname):
         if sign in tzname:
             name, offset = tzname.rsplit(sign, 1)
             if offset and parse_time(offset):
+                if ":" not in offset:
+                    offset = f"{offset}:00"
                 return name, sign, offset
     return tzname, None, None
 
diff --git a/tests/db_functions/datetime/test_extract_trunc.py b/tests/db_functions/datetime/test_extract_trunc.py
index c7571b39b0..3f13ca7989 100644
--- a/tests/db_functions/datetime/test_extract_trunc.py
+++ b/tests/db_functions/datetime/test_extract_trunc.py
@@ -1832,17 +1832,18 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
         end_datetime = timezone.make_aware(end_datetime)
         self.create_model(start_datetime, end_datetime)
         self.create_model(end_datetime, start_datetime)
-        melb = zoneinfo.ZoneInfo("Australia/Melbourne")
 
-        def assertDatetimeKind(kind):
-            truncated_start = truncate_to(start_datetime.astimezone(melb), kind, melb)
-            truncated_end = truncate_to(end_datetime.astimezone(melb), kind, melb)
+        def assertDatetimeKind(kind, tzinfo):
+            truncated_start = truncate_to(
+                start_datetime.astimezone(tzinfo), kind, tzinfo
+            )
+            truncated_end = truncate_to(end_datetime.astimezone(tzinfo), kind, tzinfo)
             queryset = DTModel.objects.annotate(
                 truncated=Trunc(
                     "start_datetime",
                     kind,
                     output_field=DateTimeField(),
-                    tzinfo=melb,
+                    tzinfo=tzinfo,
                 )
             ).order_by("start_datetime")
             self.assertSequenceEqual(
@@ -1853,15 +1854,17 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
                 ],
             )
 
-        def assertDatetimeToDateKind(kind):
-            truncated_start = truncate_to(start_datetime.astimezone(melb).date(), kind)
-            truncated_end = truncate_to(end_datetime.astimezone(melb).date(), kind)
+        def assertDatetimeToDateKind(kind, tzinfo):
+            truncated_start = truncate_to(
+                start_datetime.astimezone(tzinfo).date(), kind
+            )
+            truncated_end = truncate_to(end_datetime.astimezone(tzinfo).date(), kind)
             queryset = DTModel.objects.annotate(
                 truncated=Trunc(
                     "start_datetime",
                     kind,
                     output_field=DateField(),
-                    tzinfo=melb,
+                    tzinfo=tzinfo,
                 ),
             ).order_by("start_datetime")
             self.assertSequenceEqual(
@@ -1872,15 +1875,17 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
                 ],
             )
 
-        def assertDatetimeToTimeKind(kind):
-            truncated_start = truncate_to(start_datetime.astimezone(melb).time(), kind)
-            truncated_end = truncate_to(end_datetime.astimezone(melb).time(), kind)
+        def assertDatetimeToTimeKind(kind, tzinfo):
+            truncated_start = truncate_to(
+                start_datetime.astimezone(tzinfo).time(), kind
+            )
+            truncated_end = truncate_to(end_datetime.astimezone(tzinfo).time(), kind)
             queryset = DTModel.objects.annotate(
                 truncated=Trunc(
                     "start_datetime",
                     kind,
                     output_field=TimeField(),
-                    tzinfo=melb,
+                    tzinfo=tzinfo,
                 )
             ).order_by("start_datetime")
             self.assertSequenceEqual(
@@ -1891,6 +1896,10 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
                 ],
             )
 
+        timezones = [
+            zoneinfo.ZoneInfo("Australia/Melbourne"),
+            zoneinfo.ZoneInfo("Etc/GMT+10"),
+        ]
         date_truncations = ["year", "quarter", "month", "week", "day"]
         time_truncations = ["hour", "minute", "second"]
         tests = [
@@ -1900,8 +1909,13 @@ class DateFunctionWithTimeZoneTests(DateFunctionTests):
         ]
         for assertion, truncations in tests:
             for truncation in truncations:
-                with self.subTest(assertion=assertion.__name__, truncation=truncation):
-                    assertion(truncation)
+                for tzinfo in timezones:
+                    with self.subTest(
+                        assertion=assertion.__name__,
+                        truncation=truncation,
+                        tzinfo=tzinfo.key,
+                    ):
+                        assertion(truncation, tzinfo)
 
         qs = DTModel.objects.filter(
             start_datetime__date=Trunc(