CODEWITHSUNDEEP
sqldatabaseoracle-database
MAS1
MAS1

Reputation: 1711

How to fetch data from oracle database in hourly basis

i have one table in my database say mytable, which contents request coming from other source. There is one column in this table as Time, which stores date and time(e.g. 2010/07/10 01:21:43) when request was received. Now i want to fetch the data from this table on hourly basis for each day. Means i want count of requests database receive in each hours of a day. e.g.for 1 o'clock to 2 o'clock say count is 50 ..like this.. I will run this query at the end of day. So i will get requests received in a day group by each hour.

Can anybody help me in this.

I want query which will take less time to fetch the data as my database size is huge.

Any othre way than OMG Ponies answer.

Upvotes: 3

Views: 30156

Answers (4)

Raj Asthana
Raj Asthana

Reputation: 1

To get hourly data I am using like below:

select 
s.req_submit_date,
EXTRACT(HOUR FROM to_timestamp(s.req_submit_date,'yyyy-mm-dd hh24:mi:ss')) hr ,count(*)
from your_table s
where s.req_submit_date >= to_date('20230414', 'yyyymmdd') 
and s.req_submit_date < to_date('20230416', 'yyyymmdd')
group by s.req_submit_date,EXTRACT(HOUR FROM to_timestamp(s.req_submit_date,'yyyy-mm-dd hh24:mi:ss'))
order by s.req_submit_date,hr;

Upvotes: 0

kavehmb
kavehmb

Reputation: 9913

SELECT To_char(your_column, 'YYYY-MM-DD HH24') AS hourly, 
       Count(*)                              AS numPerHour 
FROM   your_table 
WHERE  your_column > '1-DEC-18' 
       AND your_column < '4-DEC-18' 
GROUP  BY To_char(your_column, 'YYYY-MM-DD HH24') 
ORDER  BY hourly;

Upvotes: 0

sh_kamalh
sh_kamalh

Reputation: 3901

Why don't you create another table that stores the count and the date. Create a database job that will run hourly and put the count and sysdate in the new table. Your code will be just querying the new table.

create table ohtertable_count (no_of_rows number, time_of_count date);

Your database job, that will run hourly will be something like 

insert into othertable_count 
select count(1), sysdate
from othertable;
And you will query the table othertable_count instead of querying your original table.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332631

Use the TO_CHAR function to format the DATETIME column, so you can GROUP BY it for aggregate functions:

  SELECT TO_CHAR(t.time, 'YYYY-MM-DD HH24') AS hourly,
         COUNT(*) AS numPerHour
    FROM YOUR_TABLE t
GROUP BY TO_CHAR(t.time, 'YYYY-MM-DD HH24')

Upvotes: 9

Related Questions