How to count the number of rows by datetime field with the division into time intervals

Question

I'm using Oracle Database 10g. There is a table with information about clients:

 +----+------+---------------------+
 | id | name |    registered_at    |
 +----+------+---------------------+
 |  1 | Ivan | 2016/08/01 15:09:01 |
 |  2 | Alex | 2016/08/01 16:30:21 |
 |  3 | John | 2016/08/01 16:31:05 |
 |  4 | Hugo | 2016/08/01 15:08:00 |
 |  5 | Anna | 2016/08/01 15:42:23 |
 +----+------+---------------------+

How to count records by regitered_at column with the given interval (in minutes)?

For example: interval = 10 min., datetime range from 2016/08/01 15:00:00 to 2016/08/01 17:00:00. And the result should be like:

+---------------------+---------------------+-----+
|        start_       |         end_        | cnt |
+---------------------+---------------------+-----+
| 2016/08/01 15:00:00 | 2016/08/01 15:10:00 |   2 |
| 2016/08/01 15:40:00 | 2016/08/01 15:50:00 |   1 |
| 2016/08/01 16:30:00 | 2016/08/01 16:40:00 |   2 |
+---------------------+---------------------+-----+

Table:

create table clients (
  id number(10) not null,
  name varchar2(30) not null,
  registered_at DATE
);

Data:

insert into clients 
  (id, name, registered_at) 
values 
  (1, 'Ivan', to_date('2016/08/01 15:09:01', 'YYYY/MM/DD HH24:MI:SS'));

insert into clients 
  (id, name, registered_at) 
values 
  (2, 'Alex', to_date('2016/08/01 16:30:21', 'YYYY/MM/DD HH24:MI:SS'));

insert into clients 
  (id, name, registered_at) 
values 
  (3, 'John', to_date('2016/08/01 16:31:05', 'YYYY/MM/DD HH24:MI:SS'));

insert into clients 
  (id, name, registered_at) 
values 
  (4, 'Hugo', to_date('2016/08/01 15:08:00', 'YYYY/MM/DD HH24:MI:SS'));

insert into clients 
  (id, name, registered_at) 
values 
  (5, 'Anna', to_date('2016/08/01 15:42:23', 'YYYY/MM/DD HH24:MI:SS'));

Answer 1

variable interval_minutes number;

exec :interval_minutes := 10;

select
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440, 
  count(*) 
from
  clients
group by 
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440
order by 
  1
;

Based on: Summarizing data over time - by time interval

Answer 2

SQL> select trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440 as "Start_Time",
      (trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440)+ (.000694 * 10) as "End_Time",
       count(*)
from clients
group by trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440,
         (trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440)+ (.000694 * 10);  2    3    4    5    6  

Start_Time          End_Time             COUNT(*)
------------------- ------------------- ----------
2016/08/01 15:00:00 2016/08/01 15:10:00      2
2016/08/01 16:30:00 2016/08/01 16:40:00      2
2016/08/01 15:40:00 2016/08/01 15:50:00      1

Answer 3

In case you need a more generic solution, you can use a function as this:

CREATE OR REPLACE FUNCTION MakeInterval(ts IN TIMESTAMP, roundInterval IN INTERVAL DAY TO SECOND) RETURN TIMESTAMP DETERMINISTIC IS
    denom INTEGER;
BEGIN
    IF roundInterval >= INTERVAL '1' HOUR THEN
        denom := EXTRACT(HOUR FROM roundInterval);
        IF MOD(24, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts) + TRUNC(EXTRACT(HOUR FROM ts) / denom) * denom * INTERVAL '1' HOUR;
    ELSIF roundInterval >= INTERVAL '1' MINUTE THEN
        denom := EXTRACT(MINUTE FROM roundInterval);
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'hh') + TRUNC(EXTRACT(MINUTE FROM ts) / denom) * denom * INTERVAL '1' MINUTE;
    ELSE
        denom := EXTRACT(SECOND FROM roundInterval);                
        IF MOD(60, denom) <> 0 THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'mi') + TRUNC(EXTRACT(SECOND FROM ts) / denom) * denom * INTERVAL '1' SECOND;
    END IF;
END MakeInterval;



select count(*)
   MakeInterval(registered_at, INTERVAL '10' MINUTE) as start_,  
   MakeInterval(registered_at, INTERVAL '10' MINUTE) + INTERVAL '10' MINUTE as end_
from clients 
group by MakeInterval(registered_at, INTERVAL '10' MINUTE);

Please note, the function will raise an exception if the interval does not fit in whole number, for example INTERVAL '11' MINUTE would raise an error.