[Django] #24959: date_interval_sql Implementations for Database Backends Do Not Handle Negative timedelta Objects Properly
Django
Negative timedelta Objects Properly
-------------------------------------+-------------------------------------
Reporter: fredpalmer | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.8
(models, ORM) | Keywords: date_interval_sql,
Severity: Normal | timedelta, F, orm
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
If I have a `timedelta` object in python that represents a negative
difference, e.g.:
{{{
delta = timedelta(seconds=-3600)
print delta2
-1 day, 23:00:00
}}}
The resultant SQL generated by `date_interval_sql` for the MySQL backend
would be something like:
{{{
UPDATE `scheduling_event`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-1
0:0:82800:0' DAY_MICROSECOND),
WHERE (...)
}}}
**AND** what we want is the following:
{{{
UPDATE `scheduling_event`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-0 0:0:3600:0'
DAY_MICROSECOND),
WHERE (...)
}}}
In layman's terms - the two layers are not convertible in a one-to-one
sense. A `timedelta` in for the example above in Python means: '''go back
one day and *add* 23 hours'''. So some `datetime + delta` would just
subtract one hour.
In MySQL, however, "INTERVAL '-1 0:0:82800:0' DAY_MICROSECOND)" means:
'''add a negative one day and 23 hours'''.
--
Ticket URL: <https://code.djangoproject.com/ticket/24959>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Negative timedelta Objects Properly
-------------------------------------+-------------------------------------
Reporter: fredpalmer | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage:
timedelta, F, orm | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_docs: => 0
* needs_tests: => 0
* needs_better_patch: => 0
Old description:
> If I have a `timedelta` object in python that represents a negative
> difference, e.g.:
>
> {{{
> delta = timedelta(seconds=-3600)
> print delta2
> -1 day, 23:00:00
> }}}
>
> The resultant SQL generated by `date_interval_sql` for the MySQL backend
> would be something like:
>
> {{{
> UPDATE `scheduling_event`
> SET ...
> `my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-1
> 0:0:82800:0' DAY_MICROSECOND),
> WHERE (...)
> }}}
>
> **AND** what we want is the following:
>
> {{{
> UPDATE `scheduling_event`
> SET ...
> `my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-0 0:0:3600:0'
> DAY_MICROSECOND),
> WHERE (...)
> }}}
>
> In layman's terms - the two layers are not convertible in a one-to-one
> sense. A `timedelta` in for the example above in Python means: '''go
> back one day and *add* 23 hours'''. So some `datetime + delta` would
> just subtract one hour.
>
> In MySQL, however, "INTERVAL '-1 0:0:82800:0' DAY_MICROSECOND)" means:
> '''add a negative one day and 23 hours'''.
New description:
If I have a `timedelta` object in python that represents a negative
difference, e.g.:
{{{
delta = timedelta(seconds=-3600)
print delta2
-1 day, 23:00:00
}}}
The resultant SQL generated by `date_interval_sql` for the MySQL backend
would be something like:
{{{
UPDATE `my_table`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-1
0:0:82800:0' DAY_MICROSECOND),
WHERE (...)
}}}
**AND** what we want is the following:
{{{
UPDATE `my_table`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-0 0:0:3600:0'
DAY_MICROSECOND),
WHERE (...)
}}}
In layman's terms - the two layers are not convertible in a one-to-one
sense. A `timedelta` in for the example above in Python means: '''go back
one day and *add* 23 hours'''. So some `datetime + delta` would just
subtract one hour.
In MySQL, however, ''"INTERVAL '-1 0:0:82800:0' DAY_MICROSECOND)"'' means:
'''add a negative one day and 23 hours'''.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:1>
Django
Negative timedelta Objects Properly
-------------------------------------+-------------------------------------
Reporter: fredpalmer | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage:
timedelta, F, orm | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> If I have a `timedelta` object in python that represents a negative
> difference, e.g.:
>
> {{{
> delta = timedelta(seconds=-3600)
> print delta2
> -1 day, 23:00:00
> }}}
>
> The resultant SQL generated by `date_interval_sql` for the MySQL backend
> would be something like:
>
> {{{
> UPDATE `my_table`
> SET ...
> `my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-1
> 0:0:82800:0' DAY_MICROSECOND),
> WHERE (...)
> }}}
>
> **AND** what we want is the following:
>
> {{{
> UPDATE `my_table`
> SET ...
> `my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-0 0:0:3600:0'
> DAY_MICROSECOND),
> WHERE (...)
> }}}
>
> In layman's terms - the two layers are not convertible in a one-to-one
> sense. A `timedelta` in for the example above in Python means: '''go
> back one day and *add* 23 hours'''. So some `datetime + delta` would
> just subtract one hour.
>
> In MySQL, however, ''"INTERVAL '-1 0:0:82800:0' DAY_MICROSECOND)"''
> means: '''add a negative one day and 23 hours'''.
New description:
If I have a `timedelta` object in python that represents a negative
difference, e.g.:
{{{
delta = timedelta(seconds=-3600)
print delta2
-1 day, 23:00:00
}}}
The resultant SQL generated by `date_interval_sql` for the MySQL backend
would be something like:
{{{
UPDATE `my_table`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-1
0:0:82800:0' DAY_MICROSECOND),
WHERE (...)
}}}
**AND** what we want is the following:
{{{
UPDATE `my_table`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-0 0:0:3600:0'
DAY_MICROSECOND),
WHERE (...)
}}}
In layman's terms - the two layers are not convertible in a one-to-one
sense. A `timedelta` in for the example above in Python means: '''go back
one day and *add* 23 hours'''. So some `datetime + delta` would just
subtract one hour.
In MySQL, however, ''INTERVAL '-1 0:0:82800:0' DAY_MICROSECOND)'' means:
'''add a negative one day and 23 hours'''.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:2>
Django
Negative timedelta Objects Properly
-------------------------------------+-------------------------------------
Reporter: fredpalmer | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage:
timedelta, F, orm | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> If I have a `timedelta` object in python that represents a negative
> difference, e.g.:
>
> {{{
> delta = timedelta(seconds=-3600)
> print delta2
> -1 day, 23:00:00
> }}}
>
> The resultant SQL generated by `date_interval_sql` for the MySQL backend
> would be something like:
>
> {{{
> UPDATE `my_table`
> SET ...
> `my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-1
> 0:0:82800:0' DAY_MICROSECOND),
> WHERE (...)
> }}}
>
> **AND** what we want is the following:
>
> {{{
> UPDATE `my_table`
> SET ...
> `my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-0 0:0:3600:0'
> DAY_MICROSECOND),
> WHERE (...)
> }}}
>
> In layman's terms - the two layers are not convertible in a one-to-one
> sense. A `timedelta` in for the example above in Python means: '''go
> back one day and *add* 23 hours'''. So some `datetime + delta` would
> just subtract one hour.
>
> In MySQL, however, ''INTERVAL '-1 0:0:82800:0' DAY_MICROSECOND)'' means:
> '''add a negative one day and 23 hours'''.
New description:
If I have a `timedelta` object in python that represents a negative
difference, e.g.:
{{{
delta = timedelta(seconds=-3600)
print delta2
-1 day, 23:00:00
}}}
The resultant SQL generated by `date_interval_sql` for the MySQL backend
would be something like:
{{{
UPDATE `my_table`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-1
0:0:82800:0' DAY_MICROSECOND),
WHERE (...)
}}}
**AND** what we want is the following:
{{{
UPDATE `my_table`
SET ...
`my_datetime` = (`my_table`.`my_datetime` + INTERVAL '-0 0:0:3600:0'
DAY_MICROSECOND),
WHERE (...)
}}}
In layman's terms - the two layers are not convertible in a one-to-one
sense. A `timedelta` in for the example above in Python means: '''go back
one day and *add* 23 hours'''. So some `datetime + delta` would just
subtract one hour.
In MySQL, however, ''INTERVAL '-1 0:0:82800:0' DAY_MICROSECOND'' means:
'''add a negative one day and 23 hours'''.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:3>
Django
Negative timedelta Objects Properly
-------------------------------------+-------------------------------------
Reporter: fredpalmer | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage:
timedelta, F, orm | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by timgraham):
What does your QuerySet look like?
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:4>
Django
Negative timedelta Objects Properly
-------------------------------------+-------------------------------------
Reporter: fredpalmer | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage:
timedelta, F, orm | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by fredpalmer):
Where it came up for me was a bulk `QuerySet.update()` using the `F()`
object, e.g.:
{{{
...
delta = start_current - start_previous # Get the offset to add to
filtered objects
Event.objects.filter(**filters).update(start=F("start") + delta)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:5>
Django
Negative timedelta Objects Properly
-------------------------------------+-------------------------------------
Reporter: fredpalmer | Owner: nobody
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage: Accepted
timedelta, F, orm |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Fred Palmer | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage: Accepted
timedelta, F, orm |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
I'm attaching the start of a regression test.
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Fred Palmer | Owner: nobody
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage: Accepted
timedelta, F, orm |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "24959-test.diff" added.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage: Accepted
timedelta, F, orm |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => felixxm
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Fred Palmer | Owner: felixxm
Type: Bug | Status: assigned
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage: Accepted
timedelta, F, orm |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/7571 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Fred Palmer | Owner: felixxm
Type: Bug | Status: assigned
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage: Ready for
timedelta, F, orm | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Fred Palmer | Owner: felixxm
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: date_interval_sql, | Triage Stage: Ready for
timedelta, F, orm | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"b63d0c54b05ede2589e2b720eb0c102c02891962" b63d0c54]:
{{{
#!CommitTicketReference repository=""
revision="b63d0c54b05ede2589e2b720eb0c102c02891962"
Fixed #24959 -- Fixed queries using negative timedeltas on MySQL and
Oracle.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24959#comment:11>