Change the SQL while filtering by month or day with MySQL when USE_TZ is True

[Fossen]

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 ​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 []>

​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.

[Tim Graham]

Aymeric, do you have an opinion about this?

[Aymeric Augustin]

This suggestion won't work in timezones that have DST. Most time zones do.

(Besides there's a number of other reasons why it was decided in #2626 to store datetimes in UTC in the database rather than in local time.)

[Fossen]

Replying to Aymeric Augustin:

This suggestion won't work in timezones that have DST. Most time zones do.

(Besides there's a number of other reasons why it was decided in #2626 to store datetimes in UTC in the database rather than in local time.)

Thanks for your reply
I haven't thought about DST, and I also find there are some situations that I didn't consider. I will think about it.

But I still don't think it's a good idea to convert utc time to local time in database when querying.
Django saves utc time to database,and database won't deal with timezone. But, actually, when querying datetime with filtering by month or day in MySQL, it need MySQL to

convert utc time (raw data) to local time (filtering condition), and then select the matching data. This process depends on the time zone tables which stores in database, rather than pytz. I think the timezone convertions should be handled by pytz both when saving and querying.

[Aymeric Augustin]

Please review the discussions around #2626 and ​this discussion for more information about why Django stores datetimes in UTC in the database when USE_TZ = True.

Among databases supported by Django, MySQL isn't the most convenient, complete or correct. That's unfortunate but not a sufficient reason to challenge the design. If you want to change it, please make a proposal on django-developers with a similar level of detail to the discussion I linked to.

PS - based on what you wrote, it seems to me that you could set USE_TZ = False and be happy with the result.

[Aymeric Augustin]

Re-reading that discussion, I wrote about storing UTC or local time in the database: "this horse has been beaten to death on this mailing-list so many times that I'll keep the argumentation short" which suggests there was a wide consensus on the matter in 2011 already. Dig into older discussions if you want more details.

[Fossen]

Replying to Aymeric Augustin:

Sorry for my poor expression. Time zone is indeed necessary for me, I do not wanna set USE_TZ = False.
I just wanna use time zone with MySQL without adding timezone table, just like other database. (The reason I choosing MySQL is not because I like it, but I have to, because of my job. T _T) I know django already provide a solution, but I just think the difference bewteen MySQL and other databases makes django a little more incomplete.

Thanks for you advice. I will read that discussion for more details.