Re: SQL Query: Counting records by Time Interval

From: Chris Colclough <chris.colclough_at_no_spam.jhuapl.edu>
Date: Mon, 25 Jun 2007 13:58:35 -0400
Message-ID: <f5ovoc$qqh$1@aplnetnews.jhuapl.edu>

<fitzjarrell_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. Received on Mon Jun 25 2007 - 12:58:35 CDT