TruncDay error when using offset timezones on MySQL, SQLite, and Oracle.

[Alan]

I believe there's a problem using TruncDay with a timezone off-set like 'Etc/GMT+3'. The queryset returns Null values on MySQL.

When using:

TruncDay('time', tzinfo=timezone('Etc/GMT+03:00'))

The queryset is translated into:

CONVERT_TZ(`model`.`time`, 'UTC', '+3')

Which causes the problem, as this timezone '+3' is not valid.
The timezone is formatted on though _prepare_tzname_delta method on django/db/backends/mysql/operations.py .

[Mariusz Felisiak]

Duplicate of #32992.

[Mariusz Felisiak]

Sorry for marking as a duplicate, this is a separate issue. As far as I'm aware this never worked. A naive solution may be fixing the offset format, e.g.

diff --git a/django/db/backends/mysql/operations.py b/django/db/backends/mysql/operations.py
index 89730cee29..ffa6981b7c 100644
--- a/django/db/backends/mysql/operations.py
+++ b/django/db/backends/mysql/operations.py
@@ -75,11 +75,16 @@ class DatabaseOperations(BaseDatabaseOperations):
         else:
             return "DATE(%s)" % (field_name)
 
+    def _format_tzname_offset(self, tzname):
+        if len(tzname) == 3:
+            return tzname + ':00'
+        return tzname
+
     def _prepare_tzname_delta(self, tzname):
         if '+' in tzname:
-            return tzname[tzname.find('+'):]
+            return self._format_tzname_offset(tzname[tzname.find('+'):])
         elif '-' in tzname:
-            return tzname[tzname.find('-'):]
+            return self._format_tzname_offset(tzname[tzname.find('-'):])
         return tzname
 
     def _convert_field_to_tz(self, field_name, tzname):

[Alan]

This fix the issue. Maybe the same problem is also happening with Oracle, at least the code in _prepare_tzname_delta is the same as the MySQL.

[Mariusz Felisiak]

This fix the issue. Maybe the same problem is also happening with Oracle, at least the code in _prepare_tzname_delta is the same as the MySQL.

Slice in _prepare_tzname_delta() is not important because tzname will be -03 after fixing #32992. I reproduced the same issue on Oracle and SQLite.

[Kenneth Lim]

Strictly speaking, would this be better handled by modifying the name handling instead? Here we'll convert directly from etc/GMT+9 -> "-9:00", rather than passing "etc/GMT+9" directly over to mysql

[Shafiya Adzhani]

This ticket is still open, right? I will try to understand it and create the patch.

[Shafiya Adzhani]

Added patch here: ​https://github.com/django/django/pull/17814

[Mariusz Felisiak <felisiak.mariusz@…>]

In 22285d3:

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