[Django] #28410: query filter on date portion of datetime creates wrong parameters for mysql CONVERT_TZ function and returns no results
Django
CONVERT_TZ function and returns no results
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
donaldinho |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) | Keywords: timezone date
Severity: Normal | filter
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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(`central_soccer_match`.`startDateTime`, 'UTC', '-0500')) =
'2017-07-18'
}}}
where as it should be
{{{
DATE(CONVERT_TZ(`central_soccer_match`.`startDateTime`, 'UTC', '-05:00'))
= '2017-07-18'
}}}
note the colon in the timezone we are converting to.
--
Ticket URL: <https://code.djangoproject.com/ticket/28410>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
CONVERT_TZ function and returns no results
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: timezone date | Triage Stage:
filter | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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(`central_soccer_match`.`startDateTime`, 'UTC', '-0500'))
> = '2017-07-18'
> }}}
>
> where as it should be
>
> {{{
> DATE(CONVERT_TZ(`central_soccer_match`.`startDateTime`, 'UTC', '-05:00'))
> = '2017-07-18'
> }}}
>
> note the colon in the timezone we are converting to.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28410#comment:1>
Django
CONVERT_TZ function and returns no results
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
filter |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
I can reproduce the problem, although poking around the code left me
wondering if there's a reason not to deprecate `get_fixed_timezone()` and
`FixedOffset` so we don't have to handle this additional case. Is there a
reason you're preferring that rather than using `pytz`? I didn't see an
explanation of the `get_fixed_timezone()`'s use case in Django's
documentation.
--
Ticket URL: <https://code.djangoproject.com/ticket/28410#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: donaldinho | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: timezone date | Triage Stage: Accepted
filter |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/28410#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: donaldinho | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: timezone date | Triage Stage: Accepted
filter |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by donaldinho):
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
--
Ticket URL: <https://code.djangoproject.com/ticket/28410#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: donaldinho | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: timezone date | Triage Stage: Accepted
filter |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Can Sarıgöl (added)
Comment:
In master branch, I couldn't see a problem with this query. Doesn't
{{{timedelta(hours=-5)}}} work for your need? am I wrong?
--
Ticket URL: <https://code.djangoproject.com/ticket/28410#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: donaldinho | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: timezone date | Triage Stage: Accepted
filter |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Carlton Gibson (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28410#comment:6>