#29384: Change the SQL while filtering by month or day with MySQL when USE_TZ is
True
-------------------------------------+-------------------------------------
Reporter: Fossen | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: 2.0
layer (models, ORM) |
Severity: Normal | Keywords: MySQL
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
While filtering by year works, filtering by month or by date doesn't with
MySQL when USE_TZ is True.
I know that there is already
[https://docs.djangoproject.com/en/2.0/ref/models/querysets/#database-
time-zone-definitions a solution about MySQL's timezone problem], but I
think there's a better way to improve this problem.
I come up with two reasons for changing this feature:
1. no requirements for MySQL to use timezone, which just like other
databases;
2. when django save datetime, the time zone conversions perform in python
rather than in database. It makes no sense to do such conversions in
datebase when querying. What if the time zone definitions are defferent
between database and pyzt? This may cause errors.
Here's what orm dose while filtering by month when USE_TZ is True and
using MySQL:
{{{
>>> Article.objects.filter(pub_date__month=2)
[2018-05-06 21:10:12,263] (0.001) SELECT `blog_article`.`id`,
`blog_article`.`title`, `blog_article`.`cover`, `blog_article`.`content`,
`blog_article`.`pub_date`, `blog_article`.`author_id`,
`blog_article`.`category_id`, `blog_article`.`views` FROM `blog_article`
WHERE EXTRACT(MONTH FROM CONVERT_TZ(`blog_article`.`pub_date`, 'UTC',
'Asia/Shanghai')) = 2 ORDER BY `blog_article`.`pub_date` DESC LIMIT 21;
args=(2,)
<QuerySet []>
}}}
[https://docs.djangoproject.com/en/2.0/ref/models/querysets/#month
Datetime fields are converted to the current time zone before filtering].
This requires time zone definitions in the database. SQLite, PostgreSQL
and Oracle have no requirements to do anything.
But to use the CONVERT_TZ function, MySQL needs to load the time zone
tables with mysql_tzinfo_to_sql. Overwise, filtering by month will not
work.
This feature may confuse someone who is new to django or MySQL. and in
fact many people who use MySQL just set USE_TZ to False to avoid such
problem.
I suggest that django may perform timezone conversions in Python like what
django dose with SQLite.
Instead of using time zone name directly, it could use time zone offset as
argment, just like:
{{{
# file loction: django\db\backends\mysql\operations.py
# source:
def _convert_field_to_tz(self, field_name, tzname):
if settings.USE_TZ:
field_name = "CONVERT_TZ(%s, 'UTC', '%s')" % (field_name,
tzname)
return field_name
# modification:
import pytz
def _convert_field_to_tz(self, field_name, tzname):
if settings.USE_TZ:
tz_utcoffset = pytz.timezone(tzname)._utcoffset.seconds
strftz = '+%d:%d' % (tz_utcoffset/60//60, tz_utcoffset/60%60)
field_name = "CONVERT_TZ(%s, '+00:00', '%s')" % (field_name,
strftz)
return field_name
}}}
In this way, we do not need to load the time zone tables in MySQL, and
filtering by month could work!
I think this feature is more friendly to beginners.
--
Ticket URL: <https://code.djangoproject.com/ticket/29384>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.8ffeca12ecd2fbfd308ec7664aa4555d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
