Oracle SQL group row count by time intervals of 10 minutes

Question

I have a table in oracle which contains data such as the following

created_date       details  
01-Jan-16 04:45    abcd  
01-Jan-16 04:47    efgh  
01-Jan-16 04:53    ijkl  
01-Jan-16 04:54    mnop  
01-Jan-16 04:58    qrst  

....etc

I want to be able to count the number of rows in the table for every 10 minutes e.g.

Time    count
04:40       2
04:50       3

Created Date = Timestamp, details = varchar

How would i do this?

Thanks

Solution 1

You can use TO_CHAR and SUBSTR to build the time string:

select
  substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0' as created,
  count(*)
from mytable
group by substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0'
order by substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0';

Or with a subquery (a derived table), so as to have to write the date expression only once:

select created, count(*)
from
(
  select substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0' as created
  from mytable
)
group by created
order by created;

Solution 2

One method is to extract the hour and minute and do arithmetic:

select extract(hour from created_date) as hh,
       floor(extract(minute from created_date) / 6) as min,
       count(*)
from t
group by extract(hour from created_date),
         floor(extract(minute from created_date) / 6)

Solution 3

An answer would be:

select trunc(sysdate, 'hh')+ trunc(to_char(sysdate,'mi')/10)*10/1440 from dual;

You can replace sysdate with your actual date/timestamp column and dual with your table

To understand the components, run:

select trunc(sysdate, 'hh') the_hour,
   to_char(sysdate,'mi') the_minutes,
   trunc(to_char(sysdate,'mi')/10)*10 minutes_truncated,
   trunc(to_char(sysdate,'mi')/10)*10/1440 part_of_the_day, --as 1 represents a day in oracle datetime system
   trunc(sysdate, 'hh')+ trunc(to_char(sysdate,'mi')/10)*10/1440 result
from dual;