Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Query: Counting records by Time Interval

Re: SQL Query: Counting records by Time Interval

From: <timmyojo_at_gmail.com>
Date: Wed, 27 Jun 2007 06:53:24 -0700
Message-ID: <1182952404.414540.43590@n60g2000hse.googlegroups.com>


On Jun 25, 2:12 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jun 25, 12:58 pm, "Chris Colclough"
>
>
>
>
>
> <chris.colclough_at_no_spam.jhuapl.edu> wrote:
> > <fitzjarr..._at_cox.net> wrote in message
>
> >news:1182793426.358633.167900_at_n60g2000hse.googlegroups.com...
>
> > > On Jun 25, 11:27 am, "Chris Colclough"
> > > <chris.colclough_at_no_spam.jhuapl.edu> wrote:
> > >> <timmy..._at_gmail.com> wrote in message
>
> > >>news:1182782705.156059.238380_at_q69g2000hsb.googlegroups.com...
>
> > >> >I have a table that tracks Request and Response data coming in and out
> > >> > of a system from different systems. Easch transaction originates from
> > >> > an external system, comes into the serving system as a Request (RQST),
> > >> > is TimeStamped (RQST_TM) and a response is generated (RSPN) and the
> > >> > response is TimeStamped (RPSN) and sent to the client system. Some of
> > >> > the columns in the table are as follows:
> > >> > ECTN CHARACTER 12 NOT NULLABLE
> > >> > TRX_TYPE CHARACTER 3 NOT NULLABLE
> > >> > RQST VARCHAR 600 NOT NULLABLE
> > >> > RQST_TM TIMESTAMP 10 NOT NULLABLE
> > >> > RSPN VARCHAR 600 NULLABLE
> > >> > RSPN_TM TIMESTAMP 10 NULLABLE
> > >> > ORIG_CD CHARACTER 2 NOT NULLABLE
>
> > >> > I need to be able to view the number of requests (COUNT) that come in
> > >> > by hour intervals (for example between the hours of 8 and 9am, 9 and
> > >> > 10 am, 10 and 11am, etc) for a particular day and the peak time period
> > >> > the system recieves requests. Could someone help me?
>
> > >> > Thanks,
> > >> > Tim
>
> > >> try:
>
> > >> select count(*)
> > >> from .....
> > >> group by trunc(rqst_tm, 'hh24'),- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > Which will not return the proper results as it groups by the hour and
> > > crosses date boundaries to do so.
>
> > > David Fitzjarrell
>
> > Should get proper results. See the following test:
>
> > SQL> create table a (mts timestamp);
>
> > Table created.
>
> > SQL> insert into a values (systimestamp-1);
>
> > 1 row created.
>
> > SQL> insert into a values (systimestamp);
>
> > 1 row created.
>
> > SQL> insert into a values (systimestamp+1);
>
> > 1 row created.
>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
>
> > Session altered.
>
> > SQL> select mts from a;
>
> > MTS
> > ---------------------------------------------------------------------------
> > 24-JUN-07 01.53.48.000000 PM
> > 25-JUN-07 01.53.53.556143 PM
> > 26-JUN-07 01.53.57.000000 PM
>
> > SQL> select trunc(mts, 'hh24') from a;
>
> > TRUNC(MTS,'HH24')
> > -------------------
> > 2007/06/24 13:00:00
> > 2007/06/25 13:00:00
> > 2007/06/26 13:00:00
>
> > The trunc(mts, 'hh24') does return the hour within the specified timestamp
> > value.- Hide quoted text -
>
> > - Show quoted text -
>
> I stand corrected.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Thanks guys, you helped

Tim Received on Wed Jun 27 2007 - 08:53:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US