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

3 files changed, 23 insertions, 11 deletions

diff --git a/django/db/backends/mysql/operations.py b/django/db/backends/mysql/operations.py
index 772ae1e37d..dd9f56e88d 100644
--- a/django/db/backends/mysql/operations.py
+++ b/django/db/backends/mysql/operations.py
@@ -94,8 +94,7 @@ class DatabaseOperations(BaseDatabaseOperations):
             return "TIME(%s)" % (field_name)
 
     def date_interval_sql(self, timedelta):
-        return "INTERVAL '%d 0:0:%d:%d' DAY_MICROSECOND" % (
-            timedelta.days, timedelta.seconds, timedelta.microseconds), []
+        return "INTERVAL '%06f' SECOND_MICROSECOND" % (timedelta.total_seconds()), []
 
     def format_for_duration_arithmetic(self, sql):
         if self.connection.features.supports_microsecond_precision:
diff --git a/django/db/backends/oracle/operations.py b/django/db/backends/oracle/operations.py
index 82be9c5241..58b83d88dc 100644
--- a/django/db/backends/oracle/operations.py
+++ b/django/db/backends/oracle/operations.py
@@ -93,16 +93,9 @@ WHEN (new.%(col_name)s IS NULL)
 
     def date_interval_sql(self, timedelta):
         """
-        Implements the interval functionality for expressions
-        format for Oracle:
-        INTERVAL '3 00:03:20.000000' DAY(1) TO SECOND(6)
+        NUMTODSINTERVAL converts number to INTERVAL DAY TO SECOND literal.
         """
-        minutes, seconds = divmod(timedelta.seconds, 60)
-        hours, minutes = divmod(minutes, 60)
-        days = str(timedelta.days)
-        day_precision = len(days)
-        fmt = "INTERVAL '%s %02d:%02d:%02d.%06d' DAY(%d) TO SECOND(6)"
-        return fmt % (days, hours, minutes, seconds, timedelta.microseconds, day_precision), []
+        return "NUMTODSINTERVAL(%06f, 'SECOND')" % (timedelta.total_seconds()), []
 
     def date_trunc_sql(self, lookup_type, field_name):
         # http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084
diff --git a/tests/expressions/tests.py b/tests/expressions/tests.py
index f8301b7161..c6daa504b1 100644
--- a/tests/expressions/tests.py
+++ b/tests/expressions/tests.py
@@ -1043,6 +1043,26 @@ class FTimeDeltaTests(TestCase):
         ).order_by('name')
         self.assertQuerysetEqual(over_estimate, ['e3', 'e4'], lambda e: e.name)
 
+    def test_negative_timedelta_update(self):
+        # subtract 30 seconds, 30 minutes, 2 hours and 2 days
+        experiments = Experiment.objects.filter(name='e0').annotate(
+            start_sub_seconds=F('start') + datetime.timedelta(seconds=-30),
+        ).annotate(
+            start_sub_minutes=F('start_sub_seconds') + datetime.timedelta(minutes=-30),
+        ).annotate(
+            start_sub_hours=F('start_sub_minutes') + datetime.timedelta(hours=-2),
+        ).annotate(
+            new_start=F('start_sub_hours') + datetime.timedelta(days=-2),
+        )
+        expected_start = datetime.datetime(2010, 6, 23, 9, 45, 0)
+        if connection.features.supports_microsecond_precision:
+            # subtract 30 microseconds
+            experiments = experiments.annotate(new_start=F('new_start') + datetime.timedelta(microseconds=-30))
+            expected_start += datetime.timedelta(microseconds=+746970)
+        experiments.update(start=F('new_start'))
+        e0 = Experiment.objects.get(name='e0')
+        self.assertEqual(e0.start, expected_start)
+
 
 class ValueTests(TestCase):
     def test_update_TimeField_using_Value(self):