Opened 9 years ago
Closed 8 years ago
#24959 closed Bug (fixed)
Allow using negative timedeltas in expressions on MySQL and Oracle
Reported by: | Fred Palmer | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.8 |
Severity: | Normal | Keywords: | date_interval_sql, timedelta, F, orm |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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.
Attachments (1)
Change History (12)
comment:1 by , 9 years ago
Description: | modified (diff) |
---|
comment:2 by , 9 years ago
Description: | modified (diff) |
---|
comment:3 by , 9 years ago
Description: | modified (diff) |
---|
comment:4 by , 9 years ago
comment:5 by , 9 years ago
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)
Seems like I've dealt with this in the past as well when calculating offsets for external application integration and had to simply resort to using the https://docs.python.org/2/library/datetime.html#datetime.timedelta.total_seconds property.
comment:6 by , 9 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Type: | Uncategorized → Bug |
comment:7 by , 8 years ago
Summary: | date_interval_sql Implementations for Database Backends Do Not Handle Negative timedelta Objects Properly → Allow using negative timedeltas in expressions |
---|
I'm attaching the start of a regression test.
by , 8 years ago
Attachment: | 24959-test.diff added |
---|
comment:8 by , 8 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:10 by , 8 years ago
Summary: | Allow using negative timedeltas in expressions → Allow using negative timedeltas in expressions on MySQL and Oracle |
---|---|
Triage Stage: | Accepted → Ready for checkin |
What does your QuerySet look like?