snippetMinor
How to count the number of rows by datetime field with the division into time intervals
Viewed 0 times
rowsfieldnumberthedivisionwithintotimeintervalshow
Problem
I'm using Oracle Database 10g. There is a table with information about clients:
How to count records by
For example: interval = 10 min., datetime range from
Table:
Data:
+----+------+---------------------+
| 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'));Solution
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
Code Snippets
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
;Context
StackExchange Database Administrators Q#155127, answer score: 4
Revisions (0)
No revisions yet.