Allow using negative timedeltas in expressions on MySQL and Oracle

[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.

[Tim Graham]

What does your QuerySet look like?

[Fred Palmer]

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 total_seconds() property.

[Tim Graham]

I'm attaching the start of a regression test.

[Mariusz Felisiak]

​PR

[Tim Graham <timograham@…>]

In b63d0c54:

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