6

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

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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.