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,) }}} [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