[Django] #29384: Change the SQL while filtering by month or day with MySQL when USE_TZ is True
Django
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.
Django
True
-------------------------------------+-------------------------------------
Reporter: Fossen | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):
* cc: Aymeric Augustin (added)
* type: New feature => Cleanup/optimization
* easy: 1 => 0
Comment:
Aymeric, do you have an opinion about this?
--
Ticket URL: <https://code.djangoproject.com/ticket/29384#comment:1>
Django
True
-------------------------------------+-------------------------------------
Reporter: Fossen | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: MySQL | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
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.)
--
Ticket URL: <https://code.djangoproject.com/ticket/29384#comment:2>
Django
True
-------------------------------------+-------------------------------------
Reporter: Fossen | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: MySQL | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by Fossen):
Replying to [comment:2 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29384#comment:3>
Django
True
-------------------------------------+-------------------------------------
Reporter: Fossen | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: MySQL | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by Aymeric Augustin):
Please review the discussions around #2626 and
[https://groups.google.com/forum/#!topic/django-developers/zwQju7hbG78
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29384#comment:4>
Django
True
-------------------------------------+-------------------------------------
Reporter: Fossen | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: MySQL | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29384#comment:5>
Django
True
-------------------------------------+-------------------------------------
Reporter: Fossen | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: MySQL | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by Fossen):
Replying to [comment:5 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29384#comment:6>