#24699: Add Avg('DurationField') support on Oracle
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Type: New | Status: new
feature |
Component: Database | Version: 1.8
layer (models, ORM) |
Severity: Normal | Keywords: oracle
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
From Josh in a [https://github.com/django/django/pull/4548 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 `TextField`s, so it's not unprecedented.
--
Ticket URL: <https://code.djangoproject.com/ticket/24699>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.75836f7967157dcff891070b27bb35dc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
