[Django] #24699: Add Avg('DurationField') support on Oracle

17 views
Skip to first unread message

Django

unread,
Apr 24, 2015, 8:38:23 AM4/24/15
to django-...@googlegroups.com
#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.

Django

unread,
May 22, 2015, 10:18:05 PM5/22/15
to django-...@googlegroups.com
#24699: Add Avg('DurationField') support on Oracle
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Type: New feature | Status: new
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

unread,
May 23, 2015, 4:20:50 AM5/23/15
to django-...@googlegroups.com
#24699: Add Avg('DurationField') support on Oracle
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Type: New feature | Status: new
Component: Database layer | Version: master

(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by jarshwah):

* 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

unread,
May 25, 2015, 2:06:33 PM5/25/15
to django-...@googlegroups.com
#24699: Add Avg('DurationField') support on Oracle
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timgraham):

* needs_better_patch: 0 => 1


--
Ticket URL: <https://code.djangoproject.com/ticket/24699#comment:3>

Django

unread,
May 27, 2015, 8:12:04 PM5/27/15
to django-...@googlegroups.com
#24699: Add Avg('DurationField') support on Oracle
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by jarshwah):

* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin


--
Ticket URL: <https://code.djangoproject.com/ticket/24699#comment:4>

Django

unread,
May 30, 2015, 8:45:28 PM5/30/15
to django-...@googlegroups.com
#24699: Add Avg('DurationField') support on Oracle
-------------------------------------+-------------------------------------
Reporter: timgraham | Owner: jarshwah
Type: New feature | Status: closed

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed

Keywords: oracle | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham <timograham@…>):

* 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>

Reply all
Reply to author
Forward
0 new messages