Home » SQL & PL/SQL » SQL & PL/SQL » avg function on a date field
avg function on a date field [message #243494] Thu, 07 June 2007 09:14 Go to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
I have four dates

1) 07/07/07 23:45:67
2)07/07/07 21:45:67
3)08/07/07 20:45:67
4)07/07/07 13:45:67

I need to work out the average date for the above. i have used
select avg(date field) from table1 but it doesn't work
Re: avg function on a date field [message #243495 is a reply to message #243494] Thu, 07 June 2007 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, AVG and most other grouping functions (but MIN, MAX, COUNT) work on numbers.
You have to convert your date to a number, average and convert it back to a date.

But, a question: what is the meaning of an average date?

Regards
Michel
Re: avg function on a date field [message #243498 is a reply to message #243495] Thu, 07 June 2007 09:45 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
Is there an easy way to convert date into number instead of using
Year, Month and DayOfmonth functions?
Re: avg function on a date field [message #243500 is a reply to message #243494] Thu, 07 June 2007 09:45 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
FYI, you can use the Julian date format element to convert your dates to numbers.

If you supply some formatted DDL commands for your test case along with valid test data (values like 23:45:67 are not legal times, seconds can't be >59) we can give you a more specific answer.

--
Joe Fuda
SQL Snippets
Re: avg function on a date field [message #243506 is a reply to message #243500] Thu, 07 June 2007 10:07 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
CREATE TABLE XACCT_BACKLOG2
(
STREAM VARCHAR2(30),
ID VARCHAR2(30),
DATE_LAST_COLLECTION DATE,
DATE_LATEST_PARSED DATE
);


INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DD', TO_DATE('07/06/2007 13:15:07','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:52:27','DD/MM/YYYY HH24:MI:SS'));

INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DE', TO_DATE('07/06/2007 13:15:07','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:53:10','DD/MM/YYYY HH24:MI:SS'));

INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DF', TO_DATE('07/06/2007 13:15:25','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:53:23','DD/MM/YYYY HH24:MI:SS'));

INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DH', TO_DATE('07/06/2007 13:15:27','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:53:56','DD/MM/YYYY HH24:MI:SS'));


Trying to work out the average date for date_last_collection field.
Re: avg function on a date field [message #243524 is a reply to message #243506] Thu, 07 June 2007 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still don't know:
Quote:
what is the meaning of an average date?

What do you want to do with it?

Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.

Regards
Michel
Re: avg function on a date field [message #243526 is a reply to message #243524] Thu, 07 June 2007 11:04 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Trying to work out the average date for date_last_collection field.

do you want to produce report on the basis of this coulmn?
Re: avg function on a date field [message #243536 is a reply to message #243506] Thu, 07 June 2007 12:12 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Try this.
alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;

Session altered.

select
  to_date
  ( to_char
    ( avg
      ( to_number
        ( to_char( DATE_LAST_COLLECTION, 'J.SSSSS' ) )
      ),
      '9999999.99999'
    ),
    'J.SSSSS'
  ) as avg_date
from XACCT_BACKLOG2;

AVG_DATE
-------------------
07/06/2007 13:15:17

1 row selected.




--
Joe Fuda
SQL Snippets
Re: avg function on a date field [message #243539 is a reply to message #243536] Thu, 07 June 2007 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good try but wrong:
SQL> with XACCT_BACKLOG2 as (
  2    select to_date('01/01/2007 23:59:58','DD/MM/YYYY HH24:MI:SS') DATE_LAST_COLLECTION from dual
  3    union all
  4    select to_date('02/01/2007 00:00:02','DD/MM/YYYY HH24:MI:SS') DATE_LAST_COLLECTION from dual
  5    )
  6  select
  7    to_date
  8    ( to_char
  9      ( avg
 10        ( to_number
 11          ( to_char( DATE_LAST_COLLECTION, 'J.SSSSS' ) )
 12        ),
 13        '9999999.99999'
 14      ),
 15      'J.SSSSS'
 16    ) as avg_date
 17  from XACCT_BACKLOG2
 18  /
  ( to_char
    *
ERROR at line 8:
ORA-01853: seconds in day must be between 0 and 86399

There are not 100000 seconds in a day.

Regards
Michel
Re: avg function on a date field [message #243544 is a reply to message #243494] Thu, 07 June 2007 12:59 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
subtract from a know earlier date. See the following


select to_char(to_date('01011960','mmddyyyy') + avg(date_last_collection - to_date('01011960','mmddyyyy')),'MM/dd/yyyy hh24:mi.ss') from xacct_backlog2
Re: avg function on a date field [message #243549 is a reply to message #243539] Thu, 07 June 2007 13:12 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Oops. Good point Michel. Here's a corrected version, though Bill B's solution looks simpler.

alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;

Session altered.


with XACCT_BACKLOG2 as
(
  select to_date('01/01/2007 23:59:58','DD/MM/YYYY HH24:MI:SS') DATE_LAST_COLLECTION from dual
  union all
  select to_date('02/01/2007 00:00:02','DD/MM/YYYY HH24:MI:SS') DATE_LAST_COLLECTION from dual
  )
select
  to_date( trunc( avg_1 ), 'J' ) + avg_1 - trunc( avg_1 ) as avg_date
from
( select
    avg
    ( to_number( to_char( DATE_LAST_COLLECTION, 'J' ) ) +
      ( to_number( to_char( DATE_LAST_COLLECTION, 'SSSSS' ) ) / 86400 )
    ) as avg_1
  from XACCT_BACKLOG2
);

AVG_DATE
-------------------
02/01/2007 00:00:00

1 row selected.



CREATE TABLE XACCT_BACKLOG2
(
STREAM VARCHAR2(30),
ID VARCHAR2(30),
DATE_LAST_COLLECTION DATE,
DATE_LATEST_PARSED DATE
);

Table created.



INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DD', TO_DATE('07/06/2007 13:15:07','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:52:27','DD/MM/YYYY HH24:MI:SS'));

1 row created.


INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DE', TO_DATE('07/06/2007 13:15:07','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:53:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.


INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DF', TO_DATE('07/06/2007 13:15:25','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:53:23','DD/MM/YYYY HH24:MI:SS'));

1 row created.


INSERT INTO XACCT_BACKLOG2 (stream, id, date_last_collection, date_latest_parsed)
VALUES ('FFF', 'DH', TO_DATE('07/06/2007 13:15:27','DD/MM/YYYY HH24:MI:SS'), TO_DATE('07/06/2007 09:53:56','DD/MM/YYYY HH24:MI:SS'));

1 row created.


select
  to_date( trunc( avg_1 ), 'J' ) + avg_1 - trunc( avg_1 ) as avg_date
from
( select
    avg
    ( to_number( to_char( DATE_LAST_COLLECTION, 'J' ) ) +
      ( to_number( to_char( DATE_LAST_COLLECTION, 'SSSSS' ) ) / 86400 )
    ) as avg_1
  from XACCT_BACKLOG2
);

AVG_DATE
-------------------
07/06/2007 13:15:17

1 row selected.


--
Joe Fuda
SQL Snippets
Re: avg function on a date field [message #243550 is a reply to message #243549] Thu, 07 June 2007 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, these ones work.
But I still don't understand what's the meaning of an average date. Smile

Regards
Michel
icon7.gif  Re: avg function on a date field [message #243673 is a reply to message #243550] Fri, 08 June 2007 06:01 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
Thanks guys. It's working.
I need to calculate the average date as part of a complex reporting queries. It's what the users want and they're always right Razz
Re: avg function on a date field [message #418122 is a reply to message #243549] Thu, 13 August 2009 01:39 Go to previous messageGo to next message
Sokrates
Messages: 4
Registered: August 2009
Junior Member
A much easier solution would be

SQL> select sysdate - avg( sysdate - DATE_LAST_COLLECTION ) from XACCT_BACKLOG2;

SYSDATE-AVG(SYSDATE
-------------------
07/06/2007 13:15:16



(note:
a. this solution return "..:16" instead of "..:17". Both are "correct" in the sense that the full truth "..:16.5" is in the middle and we have to round

b. this solution to compute the average of date-columns works always
)
Re: avg function on a date field [message #418127 is a reply to message #418122] Thu, 13 August 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 07 June 2007 20:14
Yes, these ones work.
But I still don't understand what's the meaning of an average date. Smile

Regards
Michel


Re: avg function on a date field [message #418135 is a reply to message #243550] Thu, 13 August 2009 02:11 Go to previous messageGo to next message
Sokrates
Messages: 4
Registered: August 2009
Junior Member
easy:

For each value, take the number of seconds since January 1, 4712 BC (to_date('1', 'J')).
Take the AVG of these numbers rounded to the next integer; the result should be THE date for which the number of seconds since January 1, 4712 BC is this AVG.
Re: avg function on a date field [message #418138 is a reply to message #418135] Thu, 13 August 2009 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not answer my question.

Regards
Michel
Re: avg function on a date field [message #418140 is a reply to message #418138] Thu, 13 August 2009 02:40 Go to previous messageGo to next message
Sokrates
Messages: 4
Registered: August 2009
Junior Member
Michel Cadot wrote on Thu, 13 August 2009 09:36
This does not answer my question.

Regards
Michel



why ?
Re: avg function on a date field [message #418143 is a reply to message #418140] Thu, 13 August 2009 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because all you have provided is a description of how to calculate an average date - the concept of what an average date actually represents is still undefined.

The nearest I can come to it is this: Suppose you were receiving a set of deliveries that were all supposed to come in on the same day, but actually arrived over several days.

It would be meaningful to calculate the average number of days late that each delivery was.
The average delivery date is (in this case) effectively the planned delivery date plus the average number of days late.

Re: avg function on a date field [message #418172 is a reply to message #418143] Thu, 13 August 2009 04:23 Go to previous messageGo to next message
Sokrates
Messages: 4
Registered: August 2009
Junior Member
JRowbottom wrote on Thu, 13 August 2009 09:48
.. Because the concept of what an average date actually represents is still undefined.
...


A question: what is the meaning of "concept" ?

I don't care what the concept of what an average date actually represents is.
What is the concept of "what an average number actually represents" ?
I agree with haiza.
It's what the users want and they're always right.
Re: avg function on a date field [message #418173 is a reply to message #418172] Thu, 13 August 2009 04:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
It's what the users want and they're always right.

Laughing Laughing Laughing

That's a good one.
Re: avg function on a date field [message #418176 is a reply to message #418172] Thu, 13 August 2009 04:32 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It's what the users want and they're always right.

And then the "final solution" (sorry to digress). Sad

Regards
Michel
Previous Topic: Store and Display website Link using plsql
Next Topic: view to be split into multiple views
Goto Forum:
  


Current Time: Mon Dec 05 18:53:13 CST 2016

Total time taken to generate the page: 0.30346 seconds