Add Avg('DurationField') support on Oracle

[Tim Graham]

From Josh in a ​PR:

Oracle doesn't allow Avg or Sum on interval data types. Seems like the commonly accepted way to do so is with EXTRACT(day from 24*60*60*interval). See ​http://stackoverflow.com/a/5063553/10583. That only gets you to second resolution though, completely ignoring microseconds. Not really ideal.

​http://www.dba-oracle.com/t_timestamp_math_elapsed_times.htm is another option which involves extracting each piece of the date component and multiplying by a constant. That'll give microsecond precision (decimal second component at least).

with the_interval as (
  select INTERVAL '4 5:12:10.999999' DAY TO SECOND(6) itv from dual
)
select 
  itv,
 -- convert back to interval after applying avg function
 NUMTODSINTERVAL(avg(extract(day from itv)*86400 +
  extract(hour from itv)*3600 +
  extract(minute from itv)*60 +
  extract(second from itv)), 'SECOND') seconds_with_precision,
  extract(day from 24*60*60*itv) seconds_without_precision
from the_interval;

To support this in AVG, we'd need to create an as_oracle() method, test if the output_field is an interval, and then wrap output_field with multiple extracts. The functions.Coalesce does something similar with TextFields, so it's not unprecedented.

[Josh Smeaton]

I'm working on this at the moment. I think there are similar issues with the SUM(interval) aggregate, so I'll do that if required too.

[Josh Smeaton]

PR: ​https://github.com/django/django/pull/4699

[Tim Graham <timograham@…>]

In c7805ee:

Fixed #24699 -- Added aggregate support for DurationField on Oracle