[Django] #24699: Add Avg('DurationField') support on Oracle
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by jarshwah):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24699#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
(models, ORM) |
Severity: Normal | Resolution:
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* version: 1.8 => master
Comment:
PR: https://github.com/django/django/pull/4699
--
Ticket URL: <https://code.djangoproject.com/ticket/24699#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/24699#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/24699#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Component: Database layer | Version: master
(models, ORM) |
Keywords: oracle | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"c7805ee214802ff1c0de53660bd2594bc1abfebb" c7805ee]:
{{{
#!CommitTicketReference repository=""
revision="c7805ee214802ff1c0de53660bd2594bc1abfebb"
Fixed #24699 -- Added aggregate support for DurationField on Oracle
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24699#comment:5>