The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Analysis of time difference between logs
 There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic. There was an error processing your request. Please try again. Standard view   View as tree
 7 messages

From:
To:
Cc:
Followup To:
Subject:
 Validation: For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon.

More options Dec 14 2011, 2:43 pm
From: Vincent <animedaydrea...@gmail.com>
Date: Wed, 14 Dec 2011 11:43:45 -0800 (PST)
Local: Wed, Dec 14 2011 2:43 pm
Subject: Analysis of time difference between logs
I know there must be a way to do this, but the answer thus far remains
elusive.  I am trying to write an odd sort of query to gather some
statistics on time differences between the records.  For example,
assume I have the following records in a table:

11/30/2011 00:00
11/30/2011 01:00
11/30/2011 02:00
11/30/2011 03:00
11/30/2011 07:00
11/30/2011 08:00
11/30/2011 09:00
11/30/2011 11:00
11/30/2011 18:00
11/30/2011 19:00
11/30/2011 20:00
11/30/2011 23:00

What I desire to calculate are the average, minimum, maximum, mode,
and standard deviation of the time differences between each
consecutive record.  So, the raw differences would be:

1 - 0 = 1
2 - 1 = 1
3 - 2 = 1
7 - 3 = 4
8 - 7 = 1
9 - 8 = 1
11 - 9 = 2
18 - 11 = 7
19 - 18 = 1
20 - 19 = 1
23 - 20 = 3

The desired resultant values would be:
Avg:  2.09
Min:  1
Max:  7
Mode:  1
Std. Dev:  1.9

I'm thinking there must be a way to do this with Oracle's analytic
functions, but I'm not having much luck wrapping my head around them.
Ideas?

To post a message you must first join this group.
You do not have the permission required to post.
More options Dec 14 2011, 4:46 pm
From: ddf <orat...@msn.com>
Date: Wed, 14 Dec 2011 13:46:46 -0800 (PST)
Local: Wed, Dec 14 2011 4:46 pm
Subject: Re: Analysis of time difference between logs

On Dec 14, 11:43 am, Vincent <animedaydrea...@gmail.com> wrote:

Lag() or lead() would help here; using lag():

from <table name here>;

will return the difference list you desire.  Wrapping that in another
query could give you the sum, avg, min, max and standard deviation of
the result set.  Remember to discard the NULL difference for the very
first record.

David Fitzjarrell

To post a message you must first join this group.
You do not have the permission required to post.
More options Dec 14 2011, 11:05 pm
From: Carlos Romeu <carlosrom...@gmail.com>
Date: Wed, 14 Dec 2011 23:05:08 -0500
Local: Wed, Dec 14 2011 11:05 pm
Subject: Re: [Ora_DBA_Exp] Re: Analysis of time difference between logs
Starting in 9i, oracle introduced 2 new function that would make this task possible ... I recommend you look for the lead & lag functions

Sent from my iPhone

On Dec 14, 2011, at 4:46 PM, ddf <orat...@msn.com> wrote:

To post a message you must first join this group.
You do not have the permission required to post.
More options Dec 14 2011, 9:52 pm
From: Vincent <animedaydrea...@gmail.com>
Date: Wed, 14 Dec 2011 18:52:48 -0800 (PST)
Local: Wed, Dec 14 2011 9:52 pm
Subject: Re: Analysis of time difference between logs
Great!  But, how would I discard the NULL difference in the first
record?  I keep getting an ORA-01722 error when I run the query and
I'm fairly certain this is due to a NULL in the first lag evaluation.

Thanks.

On Dec 14, 4:46 pm, ddf <orat...@msn.com> wrote:

To post a message you must first join this group.
You do not have the permission required to post.
More options Dec 14 2011, 11:29 pm
From: ddf <orat...@msn.com>
Date: Wed, 14 Dec 2011 20:29:08 -0800 (PST)
Local: Wed, Dec 14 2011 11:29 pm
Subject: Re: Analysis of time difference between logs

On Dec 14, 6:52 pm, Vincent <animedaydrea...@gmail.com> wrote:

The error is not due to a NULL it's due to how you have received_date
defined.  Normally date subtraction is valid; are these values
timestamps rather than dates?

David Fitzjarrell

To post a message you must first join this group.
You do not have the permission required to post.
More options Dec 15 2011, 1:36 am
From: Ashwani Kamboj <kamboj.ashw...@gmail.com>
Date: Thu, 15 Dec 2011 12:06:52 +0530
Local: Thurs, Dec 15 2011 1:36 am
Subject: Re: [Ora_DBA_Exp] Analysis of time difference between logs

Hi Vincent,

this is working fine in this scenario.at my end

SQL> desc dttest
Name                                      Null?    Type
----------------------------------------- --------
----------------------------
RCD                                                DATE

rcd
11/30/2011 00:00
11/30/2011 01:00
11/30/2011 02:00
11/30/2011 03:00
11/30/2011 07:00
11/30/2011 08:00
11/30/2011 09:00
11/30/2011 11:00
11/30/2011 18:00
11/30/2011 19:00
11/30/2011 20:00

SQL ---

(select rcd,to_char(rcd,'hh24') hr,lag(to_char(rcd,'hh24')) over (order by
rcd) dd ,(to_char(rcd,'hh24'))-(lag(to_char(rcd,'hh24')) over (order by
rcd)) Diff from dttest )

Output -

RCD             HR         DD               DIFF
--------------- ---------- ---------- ----------
30-NOV-11       00
30-NOV-11       01         00                  1
30-NOV-11       02         01                  1
30-NOV-11       03         02                  1
30-NOV-11       07         03                  4
30-NOV-11       08         07                  1
30-NOV-11       09         08                  1
30-NOV-11       11         09                  2
30-NOV-11       18         11                  7
30-NOV-11       19         18                  1
30-NOV-11       20         19                  1
30-NOV-11       23         20                  3

use can use nvl to fill first null value, and can further grouping on DIFF

Brgds

Ashwani Kamboj

To post a message you must first join this group.
You do not have the permission required to post.
More options Dec 15 2011, 9:40 am
From: Vincent <animedaydrea...@gmail.com>
Date: Thu, 15 Dec 2011 06:40:11 -0800 (PST)
Local: Thurs, Dec 15 2011 9:40 am
Subject: Re: Analysis of time difference between logs
That was the problem.  I have a workaround in place now and it is
working as intended.

Thanks!

On Dec 14, 11:29 pm, ddf <orat...@msn.com> wrote: