id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29384	Change the SQL while filtering by month or day with MySQL when USE_TZ is True	Fossen	nobody	"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."	Cleanup/optimization	closed	Database layer (models, ORM)	2.0	Normal	invalid	MySQL	Aymeric Augustin	Unreviewed	0	0	0	0	0	0