Question:
I need to make a query that returns all records grouped in certain periods. The table contains a date type and should produce output similar to this:
| Hora | Quantidade |
-------------------------
01 | 08:00 | 30 |
02 | 08:05 | 28 |
03 | 08:10 | 32 |
04 | 08:15 | 53 |
That is:
In line 1, 30 records were found with the column date between 08:00:00 and 08:04:59, in line 2, 28 records were found with the column date between 08:05:00 and 08:09:59, so on. .
Is there any way to group the date by period as described in the table?
PS: The DBMS is Oracle.
Answer:
To group by an interval, truncate the date in the unit of that interval (minute, in your case), divide by the total of one day and add with the date and time:
(trunc(current_date, 'hh')+trunc(to_char(current_date,'mi')/5)*5/1440)
In this SQL Fiddle I created a table with some timestamps for testing, in the column named d :
> select * from datas;
| D |
|-----------------------|
| 2017-10-01 08:00:00.0 |
| 2017-10-01 08:01:00.0 |
| 2017-10-01 08:03:00.0 |
| 2017-10-01 08:07:00.0 |
| 2017-10-01 08:08:00.0 |
| 2017-10-01 08:09:59.0 |
| 2017-10-01 08:11:00.0 |
| 2017-10-01 08:11:15.0 |
| 2017-10-01 08:13:00.0 |
| 2017-10-01 08:17:00.0 |
For this table I apply the above method, both to obtain the five-minute period and to the GROUP BY clause and for a window, in order to obtain the register numerator that appears in your question:
select
row_number() over (order by (trunc(d, 'hh')+trunc(to_char(d,'mi')/5)*5/1440)) as " ",
to_char((trunc(d, 'hh')+trunc(to_char(d,'mi')/5)*5/1440), 'HH24:MI') as "Hora",
count(*) as "Quantidade"
from datas
group by (trunc(d, 'hh')+trunc(to_char(d,'mi')/5)*5/1440);
| | Hora | Quantidade |
|---|-------|------------|
| 1 | 08:00 | 3 |
| 2 | 08:05 | 3 |
| 3 | 08:10 | 3 |
| 4 | 08:15 | 1 |