Opened 4 years ago

Closed 4 years ago

#24699 closed New feature (fixed)

Add Avg('DurationField') support on Oracle

Reported by: Tim Graham Owned by: Josh Smeaton
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: oracle
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

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.

Change History (5)

comment:1 Changed 4 years ago by 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.

comment:2 Changed 4 years ago by Josh Smeaton

Has patch: set
Version: 1.8master

comment:3 Changed 4 years ago by Tim Graham

Patch needs improvement: set

comment:4 Changed 4 years ago by Josh Smeaton

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:5 Changed 4 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: newclosed

In c7805ee:

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

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