Opened 7 years ago
Last modified 3 years ago
#28410 new Bug
query filter on date portion of datetime creates wrong parameters for mysql CONVERT_TZ function when used with a fixed offset timezone
Reported by: | donaldinho | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.11 |
Severity: | Normal | Keywords: | timezone date filter |
Cc: | Can Sarıgöl, Carlton Gibson | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
I am setting the timezone
tz = timezone.get_fixed_timezone(-300) timezone.activate(tz)
then I apply the filter. search_date is just a string submitted by the client
matches = query.filter(startDateTime__date=datetime.strptime(search_date, '%Y-%m-%d').date())
the where clause that is produced is
DATE(CONVERT_TZ(`match`.`startDateTime`, 'UTC', '-0500')) = '2017-07-18'
where as it should be
DATE(CONVERT_TZ(match`.`startDateTime`, 'UTC', '-05:00')) = '2017-07-18'
note the colon in the timezone we are converting to.
Change History (6)
comment:1 by , 7 years ago
Description: | modified (diff) |
---|
comment:2 by , 7 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:3 by , 7 years ago
Summary: | query filter on date portion of datetime creates wrong parameters for mysql CONVERT_TZ function and returns no results → query filter on date portion of datetime creates wrong parameters for mysql CONVERT_TZ function when used with a fixed offset timezone |
---|
comment:4 by , 7 years ago
I think I used get_fixed_timezone because I had the clients offset provided by javascript in minutes rather than an explicit timezone and I couldn't see a way to create a tzinfo class any other way
comment:5 by , 5 years ago
Cc: | added |
---|
In master branch, I couldn't see a problem with this query. Doesn't timedelta(hours=-5)
work for your need? am I wrong?
from datetime import timedelta, timezone as datetime_timezone tz = datetime_timezone(timedelta(hours=-5)) timezone.activate(tz)
comment:6 by , 3 years ago
Cc: | added |
---|
I can reproduce the problem, although poking around the code left me wondering if there's a reason not to deprecate
get_fixed_timezone()
andFixedOffset
so we don't have to handle this additional case. Is there a reason you're preferring that rather than usingpytz
? I didn't see an explanation of theget_fixed_timezone()
's use case in Django's documentation.