Opened 9 years ago

Closed 7 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 Fred Palmer)

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)

24959-test.diff (677 bytes ) - added by Tim Graham 7 years ago.

Download all attachments as: .zip

Change History (12)

comment:1 by Fred Palmer, 9 years ago

Description: modified (diff)

comment:2 by Fred Palmer, 9 years ago

Description: modified (diff)

comment:3 by Fred Palmer, 9 years ago

Description: modified (diff)

comment:4 by Tim Graham, 9 years ago

What does your QuerySet look like?

comment:5 by Fred Palmer, 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.

Last edited 9 years ago by Fred Palmer (previous) (diff)

comment:6 by Tim Graham, 9 years ago

Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

comment:7 by Tim Graham, 7 years ago

Summary: date_interval_sql Implementations for Database Backends Do Not Handle Negative timedelta Objects ProperlyAllow using negative timedeltas in expressions

I'm attaching the start of a regression test.

by Tim Graham, 7 years ago

Attachment: 24959-test.diff added

comment:8 by Mariusz Felisiak, 7 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:9 by Mariusz Felisiak, 7 years ago

Has patch: set

comment:10 by Tim Graham, 7 years ago

Summary: Allow using negative timedeltas in expressionsAllow using negative timedeltas in expressions on MySQL and Oracle
Triage Stage: AcceptedReady for checkin

comment:11 by Tim Graham <timograham@…>, 7 years ago

Resolution: fixed
Status: assignedclosed

In b63d0c54:

Fixed #24959 -- Fixed queries using negative timedeltas on MySQL and Oracle.

Note: See TracTickets for help on using tickets.
Back to Top