Opened 3 years ago

Closed 3 months ago

#33037 closed Bug (fixed)

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

Reported by: Alan Owned by: Shafiya Adzhani
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: mysql truncdate timezone
Cc: Triage Stage: Ready for checkin
Has patch: yes 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 (10)

comment:1 by Mariusz Felisiak, 3 years ago

Easy pickings: unset
Resolution: duplicate
Status: newclosed

Duplicate of #32992.

comment:2 by Mariusz Felisiak, 3 years ago

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 by Alan, 3 years ago

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 by Mariusz Felisiak, 3 years ago

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.

comment:5 by Kenneth Lim, 17 months ago

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

comment:6 by Shafiya Adzhani, 4 months ago

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

comment:7 by Shafiya Adzhani, 4 months ago

Owner: changed from nobody to Shafiya Adzhani
Status: newassigned

comment:8 by Shafiya Adzhani, 4 months ago

Has patch: set

comment:9 by Mariusz Felisiak, 3 months ago

Triage Stage: AcceptedReady for checkin

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 3 months ago

Resolution: fixed
Status: assignedclosed

In 22285d3:

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

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