Group periods of dates

Question

I need to do a query that returns all records grouped at certain periods. The table contains a date type and must produce an output similar to this:

   | Hora  | Quantidade |
-------------------------
01 | 08:00 | 30         |
02 | 08:05 | 28         |
03 | 08:10 | 32         |
04 | 08:15 | 53         |

That is to say:

Row 1 found 30 records with the column date between 08:00:00 and 08:04:59, row 2 found 28 records with the column date between 08:05:00 and 08:09:59, so successively.

Have some way to group the date by period as described in the table?

PS: DBMS is Oracle.

Answer 1

To group by an interval, truncate the date in the unit of that interval (minute, in your case), divide by the total of a day and disappear 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 name column 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 for the five-minute period and for the clause GROUP BY and to a window in order to obtain the numbering records 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 |

Answer 2

An efficient way to do this is to get the values per column instead of rows, since you already know beforehand the desired interval of hours. This way the query is fast, because it will be passed only once in each record. Otherwise (per line) you would need to do a subquery and return the lines filtering each hour interval separately, getting much slower and much bigger your select.

select 
 sum(case when to_char(campodata, 'hh:mm:ss') between '08:00:00' and '08:04:59' then 1 else 0 end) as "08:00"
,sum(case when to_char(campodata, 'hh:mm:ss') between '08:05:00' and '08:09:59' then 1 else 0 end) as "08:05"
,sum(case when to_char(campodata, 'hh:mm:ss') between '08:10:00' and '08:14:59' then 1 else 0 end) as "08:10"
,sum(case when to_char(campodata, 'hh:mm:ss') between '08:15:00' and '08:19:59' then 1 else 0 end) as "08:15"
from tabela

Answer 3

select Sum(campodata), compodata from tabela group by campodata;

the sum will give you the sum and the group by will group;