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.

---

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')

---

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.

---

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;