How to get data from oracle hourly database

Question

I have one table in my database that says mytable, the content request of which comes from another source. This table has one “Time” column, which stores the date and time (for example, 2010/07/10 01:21:43) when the request was received. Now I want to receive data from this table hourly for each day. So, I want the number of requests to be received at every hour of the day. for example, 1 hour before 2 hours, let's say the score is 50 .. like this .. I ran this request at the end of the day. Therefore, I receive requests received in the daily group every hour.

Can anyone help me on this.

I want to get a query that takes less time to retrieve data, since the size of my database is huge.

Any other way than OMG Ponies will answer.

Answer 1

Use the TO_CHAR function to format the DATETIME column so you can use GROUP BY 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')

Answer 2

Why don't you create another table that stores the counter and date. Create a database job that runs hourly and put the invoice and sysdate in a new table. Your code will just query the new table.

create table ohtertable_count (no_of_rows number, time_of_count date);

Your work with a database that will work 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.

Answer 3

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;