SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to retrieve last one hour data from table ?

871594Jun 10 2013 — edited Jul 15 2013

Hi All,

We have a table DCTRMAIN which have elapse time of different services of application. i want to see the average response time of each of service in last hour. in below table STARTDATE is the column to store response timestamp and TRANNAME stores service name.

desc DCTRMAIN
Name                           Null     Type
------------------------------ -------- --------------------------------------------------------------------------------------
APPID                                     VARCHAR2(16 CHAR)
HOSTID                                  VARCHAR2(80 CHAR)
CHANNEL_TYPE                            VARCHAR2(16 CHAR)
STARTDATE                               DATE

USERNAME                                VARCHAR2(64 CHAR)
RESP_TIME                               NUMBER(7,3)
SOURCENAME                              VARCHAR2(64 CHAR)
FUNCTIONNAME                            VARCHAR2(64 CHAR)
TRANNAME                                VARCHAR2(64 CHAR)
STATUS                                  NUMBER(10)

i am quering as below. But its giving some error.

select tranname,avg(resp_time) from DCTRMAIN where startdate between (to_char(sysdate,'DD-MON-YY HHMMSS') and to_char(sysdate-1/24,'DD-MON-YY HHMMSS') group by tranname ;

Please correct me above query .

Thanks,

Venkat Vadlamudi.

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 12 2013

Added on Jun 10 2013

5 comments

37,855 views