Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!w24g2000prd.googlegroups.com!not-for-mail From: jeremy Newsgroups: comp.databases.oracle.server Subject: Re: Looking for "gaps" (from a time perspective) in data Date: Fri, 10 Oct 2008 07:27:37 -0700 (PDT) Organization: http://groups.google.com Lines: 27 Message-ID: References: <1fe20455-c2f4-4499-b0e5-bc53b989589e@s9g2000prg.googlegroups.com> NNTP-Posting-Host: 193.164.109.10 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1223648857 7405 127.0.0.1 (10 Oct 2008 14:27:37 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 10 Oct 2008 14:27:37 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: w24g2000prd.googlegroups.com; posting-host=193.164.109.10; posting-account=h5IA6woAAABcHbb4vAIZFk2TfZkZ_eqe User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3,gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:448574 X-Received-Date: Fri, 10 Oct 2008 10:27:37 EDT (text.usenetserver.com) On Oct 10, 1:04=A0pm, ca111...@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