Fixed #24033 -- Use interval type on Oracle.

Use INTERVAL DAY(9) TO SECOND(6) for Durationfield on Oracle rather than
storing as a NUMBER(19) of microseconds.

There are issues with cx_Oracle which require some extra data
manipulation in the database backend when constructing queries, but it
handles the conversion back to timedelta objects cleanly.

Thanks to Shai for the review.

8 files changed, 25 insertions, 10 deletions

diff --git a/django/db/backends/__init__.py b/django/db/backends/__init__.py
index 104744b220..eb70ad9861 100644
--- a/django/db/backends/__init__.py
+++ b/django/db/backends/__init__.py
@@ -580,6 +580,12 @@ class BaseDatabaseFeatures(object):
     # Is there a true datatype for timedeltas?
     has_native_duration_field = False
 
+    # Does the database driver support timedeltas as arguments?
+    # This is only relevant when there is a native duration field.
+    # Specifically, there is a bug with cx_Oracle:
+    # https://bitbucket.org/anthony_tuininga/cx_oracle/issue/7/
+    driver_supports_timedelta_args = False
+
     # Do time/datetime fields have microsecond precision?
     supports_microsecond_precision = True
 
diff --git a/django/db/backends/oracle/base.py b/django/db/backends/oracle/base.py
index 9f275b785d..aadc7c9682 100644
--- a/django/db/backends/oracle/base.py
+++ b/django/db/backends/oracle/base.py
@@ -62,6 +62,7 @@ from django.db.backends.oracle.introspection import DatabaseIntrospection
 from django.db.backends.oracle.schema import DatabaseSchemaEditor
 from django.db.utils import InterfaceError
 from django.utils import six, timezone
+from django.utils.duration import duration_string
 from django.utils.encoding import force_bytes, force_text
 from django.utils.functional import cached_property
 
@@ -106,6 +107,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     supports_timezones = False
     has_zoneinfo_database = pytz is not None
     supports_bitwise_or = False
+    has_native_duration_field = True
     can_defer_constraint_checks = True
     supports_partially_nullable_unique_constraints = False
     truncates_names = True
@@ -212,9 +214,6 @@ WHEN (new.%(col_name)s IS NULL)
         return fmt % (days, hours, minutes, seconds, timedelta.microseconds,
                 day_precision), []
 
-    def format_for_duration_arithmetic(self, sql):
-        return "NUMTODSINTERVAL(%s / 1000000, 'SECOND')" % sql
-
     def date_trunc_sql(self, lookup_type, field_name):
         # http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084
         if lookup_type in ('year', 'month'):
@@ -796,6 +795,11 @@ class OracleParam(object):
                 param = timezone.make_aware(param, default_timezone)
             param = Oracle_datetime.from_datetime(param.astimezone(timezone.utc))
 
+        if isinstance(param, datetime.timedelta):
+            param = duration_string(param)
+            if ' ' not in param:
+                param = '0 ' + param
+
         string_size = 0
         # Oracle doesn't recognize True and False correctly in Python 3.
         # The conversion done below works both in 2 and 3.
diff --git a/django/db/backends/oracle/creation.py b/django/db/backends/oracle/creation.py
index ae4f5f42ec..27f0e5333b 100644
--- a/django/db/backends/oracle/creation.py
+++ b/django/db/backends/oracle/creation.py
@@ -29,7 +29,7 @@ class DatabaseCreation(BaseDatabaseCreation):
         'DateField': 'DATE',
         'DateTimeField': 'TIMESTAMP',
         'DecimalField': 'NUMBER(%(max_digits)s, %(decimal_places)s)',
-        'DurationField': 'NUMBER(19)',
+        'DurationField': 'INTERVAL DAY(9) TO SECOND(6)',
         'FileField': 'NVARCHAR2(%(max_length)s)',
         'FilePathField': 'NVARCHAR2(%(max_length)s)',
         'FloatField': 'DOUBLE PRECISION',
diff --git a/django/db/backends/postgresql_psycopg2/base.py b/django/db/backends/postgresql_psycopg2/base.py
index 5fb5d68b5b..c1dfa54c95 100644
--- a/django/db/backends/postgresql_psycopg2/base.py
+++ b/django/db/backends/postgresql_psycopg2/base.py
@@ -48,6 +48,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     can_return_id_from_insert = True
     has_real_datatype = True
     has_native_duration_field = True
+    driver_supports_timedelta_args = True
     can_defer_constraint_checks = True
     has_select_for_update = True
     has_select_for_update_nowait = True
diff --git a/django/db/models/expressions.py b/django/db/models/expressions.py
index de0d339af7..ac69307edc 100644
--- a/django/db/models/expressions.py
+++ b/django/db/models/expressions.py
@@ -488,7 +488,8 @@ class Value(ExpressionNode):
 
 class DurationValue(Value):
     def as_sql(self, compiler, connection):
-        if connection.features.has_native_duration_field:
+        if (connection.features.has_native_duration_field and
+                connection.features.driver_supports_timedelta_args):
             return super(DurationValue, self).as_sql(compiler, connection)
         return connection.ops.date_interval_sql(self.value)
 
diff --git a/django/db/models/fields/__init__.py b/django/db/models/fields/__init__.py
index 92ad769e4c..e8e6750f1e 100644
--- a/django/db/models/fields/__init__.py
+++ b/django/db/models/fields/__init__.py
@@ -1577,7 +1577,8 @@ class DecimalField(Field):
 class DurationField(Field):
     """Stores timedelta objects.
 
-    Uses interval on postgres, bigint of microseconds on other databases.
+    Uses interval on postgres, INVERAL DAY TO SECOND on Oracle, and bigint of
+    microseconds on other databases.
     """
     empty_strings_allowed = False
     default_error_messages = {
diff --git a/docs/ref/models/fields.txt b/docs/ref/models/fields.txt
index 203241ae5f..5eabf84cad 100644
--- a/docs/ref/models/fields.txt
+++ b/docs/ref/models/fields.txt
@@ -551,7 +551,8 @@ The default form widget for this field is a :class:`~django.forms.TextInput`.
 
 A field for storing periods of time - modeled in Python by
 :class:`~python:datetime.timedelta`. When used on PostgreSQL, the data type
-used is an ``interval``, otherwise a ``bigint`` of microseconds is used.
+used is an ``interval`` and on Oracle the data type is ``INTERVAL DAY(9) TO
+SECOND(6)``. Otherwise a ``bigint`` of microseconds is used.
 
 .. note::
 
diff --git a/docs/releases/1.8.txt b/docs/releases/1.8.txt
index 25d71e1235..77609ed7b5 100644
--- a/docs/releases/1.8.txt
+++ b/docs/releases/1.8.txt
@@ -60,9 +60,10 @@ New data types
 
 * Django now has a :class:`~django.db.models.DurationField` for storing periods
   of time - modeled in Python by :class:`~python:datetime.timedelta`. It is
-  stored in the native ``interval`` data type on PostgreSQL and as a ``bigint``
-  of microseconds on other backends. Date and time related arithmetic has also
-  been improved on all backends. There is a corresponding :class:`form field
+  stored in the native ``interval`` data type on PostgreSQL, as a ``INTERVAL
+  DAY(9) TO SECOND(6)`` on Oracle, and as a ``bigint`` of microseconds on other
+  backends. Date and time related arithmetic has also been improved on all
+  backends. There is a corresponding :class:`form field
   <django.forms.DurationField>`.
 
 Query Expressions