Re: Looking for "gaps" (from a time perspective) in data

From: jeremy <jeremy0505_at_gmail.com>
Date: Fri, 10 Oct 2008 07:27:37 -0700 (PDT)
Message-ID: <b990fabd-d34b-4d0a-8768-d64146fcf322@w24g2000prd.googlegroups.com>


On Oct 10, 1:04 pm, ca111..._at_gmail.com wrote:
> Hi Jeremy,
>
> Instead of looking at individual records it may be easier to analyze
> the issue from statistical perspective.
> Calculate number of records, average duration, and standard deviation
> with averaging
> over second/minute/hour - whatever is appropriate.
>
> For example, for second
>
> select
> to_char(date_start,'YYYY-MM-DD HH24:MI:SS') d_start,
> count(*) cnt,
> avg((date_end - date_start)*24*3600) avg_duration
> from tableA
> group by
> to_char(date_start,'YYYY-MM-DD HH24:MI:SS');

Hi this makes sense, but for us to identify periods of no activity, we would want to get a row for every minute if the day (obviosuly for that purpose we would start by altering the date format masks to not include the :SS) - can't think at the moment how we could do that - any thoughts?

--
jeremy
Received on Fri Oct 10 2008 - 09:27:37 CDT

Original text of this message