Opened 3 years ago
Closed 7 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 , 3 years ago
Easy pickings: | unset |
---|---|
Resolution: | → duplicate |
Status: | new → closed |
comment:2 by , 3 years ago
Resolution: | duplicate |
---|---|
Status: | closed → new |
Triage Stage: | Unreviewed → Accepted |
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 , 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 , 3 years ago
Summary: | TruncDay error when using offset timezones on MySQL → TruncDay 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 , 20 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 , 8 months ago
This ticket is still open, right? I will try to understand it and create the patch.
comment:7 by , 8 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:8 by , 7 months ago
Has patch: | set |
---|
Added patch here: https://github.com/django/django/pull/17814
comment:9 by , 7 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
Duplicate of #32992.