[Django] #33037: TruncDay error when using offset timezones on MySQL
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: dev
layer (models, ORM) | Keywords: mysql truncdate
Severity: Normal | timezone
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
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 .
--
Ticket URL: <https://code.djangoproject.com/ticket/33037>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: mysql truncdate | Triage Stage:
timezone | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => duplicate
* easy: 1 => 0
Comment:
Duplicate of #32992.
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Component: Database layer | Version: dev
(models, ORM) |
Keywords: mysql truncdate | Triage Stage:
timezone | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* resolution: duplicate => invalid
Comment:
`+3` and `-3` (after fixing #32992) both works for me on MySQL. Your issue
is probably caused by [https://docs.djangoproject.com/en/3.2/ref/databases
/#time-zone-definitions an empty time zone table in the MySQL database]
(see also a [https://docs.djangoproject.com/en/3.2/ref/models/querysets
/#database-time-zone-definitions warning]).
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
timezone |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* status: closed => new
* resolution: duplicate =>
* stage: Unreviewed => Accepted
Comment:
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):
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql truncdate | Triage Stage: Accepted
timezone |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql truncdate | Triage Stage: Accepted
timezone |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql truncdate | Triage Stage: Accepted
timezone |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by 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
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql truncdate | Triage Stage: Accepted
timezone |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
This ticket is still open, right? I will try to understand it and create
the patch.
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:6>
Django
-------------------------------------+-------------------------------------
| Adzhani
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql truncdate | Triage Stage: Accepted
timezone |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* owner: nobody => Shafiya Adzhani
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: Shafiya
| Adzhani
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql truncdate | Triage Stage: Accepted
timezone |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Shafiya Adzhani):
* has_patch: 0 => 1
Comment:
Added patch here: https://github.com/django/django/pull/17814
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: Shafiya
| Adzhani
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
timezone | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Alan | Owner: Shafiya
| Adzhani
Component: Database layer | Version: dev
(models, ORM) |
Keywords: mysql truncdate | Triage Stage: Ready for
timezone | checkin
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"22285d366c9061a668003638613685f5a135a4c3" 22285d3]:
{{{#!CommitTicketReference repository=""
revision="22285d366c9061a668003638613685f5a135a4c3"
Fixed #33037 -- Fixed Trunc() with offset timezones on MySQL, SQLite,
Oracle.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33037#comment:10>