Marllon Nasser Marllon Nasser - 5 months ago 14
SQL Question

Filter date with intervals of 1 hour between two random dates in the same day

The rule

Any records between one hour should be counted as one.

Data

ID DATE

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 date

5 06/07/2017


Why? Based on the minimum date, the records between one hour after are counted as one. Something like this:

count range_date

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:42


Any suggestions to do so?
Something in one statment since I don't have a rule for the dates (min/max). I just know that all dates it's on the same day.

And I dont want to make N selects between every hour...

Answer

If I correctly understood your problem, this is a query that obtain the result you're looking for:

SELECT 
    TRUNC(dt) AS day, 
    COUNT(DISTINCT TRUNC(dt - 20 / (24 * 60) - (35 + TO_NUMBER(TO_CHAR(dt, 'HH24')) - 9) / (24 * 60 * 60), 'HH24')) AS hours
FROM yourtable
GROUP BY TRUNC(dt)
  • TRUNC(dt, 'HH24') truncates the date to hour (minutes and seconds are set to 0)
  • I subtract the minutes and seconds of starting hour to "shift" the TRUNC to correct time period
  • TO_NUMBER(TO_CHAR(dt, 'HH24')) - 9) is mandatory to add one second for every hour
  • With COUNT DISTINCT you count the number of different hours.
  • If the initial hour is variable (as I suppose) I think the easiest way is to keep it with separate query, extract hour, minute and second and use them as variables in input to the main query