Filter date by time intervals until the last possible hour within the same day

Navigation
0

The rule

All records that are within a 1-hour interval should be counted as 1 records only.

Data 

ID     DATA

1      06/07/2017 09:20:35
2      06/07/2017 10:20:35
3      06/07/2017 10:25:30
4      06/07/2017 10:40:35
5      06/07/2017 10:50:35
6      06/07/2017 11:25:30
7      06/07/2017 11:50:20
8      06/07/2017 15:25:30
9      06/07/2017 17:25:30
10     06/07/2017 17:30:30
11     06/07/2017 17:40:55

Expected result 

count       data

5           06/07/2017

Why? Because from the earliest date, records that are "in" for up to an hour after that date count as 1 only. More or less this: 

count       range_data

1           09:20:35 - 10:20:35
1           10:20:36 - 11:20:36
1           11:20:37 - 12:20:37
0           12:20:38 - 13:20:38
0           13:20:39 - 14:20:39
0           14:20:40 - 15:20:40
1           15:20:41 - 16:20:41
1           17:20:42 - 18:20:41

Any suggestions for doing this? Without being with interactivity (for / while). Because I can have N dates in the middle, I do not have a "default" of minimum and maximum dates ... the only thing I know is that the dates are within the same day.

I do not want to have to make N selects to count between hour and time ...

    
asked by anonymous 06.07.2016 / 15:47

2 answers

0

Answer given in stackoverflow 

SELECT 
    TRUNC(dt) AS data, 
    COUNT(DISTINCT TRUNC(dt - 20 / (24 * 60) - (35 + TO_NUMBER(TO_CHAR(dt, 'HH24')) - 9) / (24 * 60 * 60), 'HH24')) AS qtd
FROM tabela
GROUP BY TRUNC(dt)
    
06.07.2016 / 16:53
0

Look like this: 

SELECT * FROM TESTE


SELECTCOUNT(*),MIN(DATA)FROMTESTEGROUPBY(DAY(DATA)*100000000)+(MONTH(DATA)*1000000)+YEAR(DATA)

Is not this what was expected, according to the question?

    
06.07.2016 / 16:46
Bootstrap - .table-striped class does not work correctly within a while Help with AsyncTask and progressbar