Opened 4 months ago

Last modified 3 months ago

#33037 new Bug

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

Reported by: Alan Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: mysql truncdate timezone
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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 .

Change History (4)

comment:1 Changed 4 months ago by Mariusz Felisiak

Easy pickings: unset
Resolution: duplicate
Status: newclosed

Duplicate of #32992.

comment:2 Changed 3 months ago by Mariusz Felisiak

Resolution: duplicate
Status: closednew
Triage Stage: UnreviewedAccepted

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):

comment:3 Changed 3 months ago by 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.

comment:4 Changed 3 months ago by Mariusz Felisiak

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

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.

Note: See TracTickets for help on using tickets.
Back to Top