CODEWITHSUNDEEP
sqloracle-database
Marllon Nasser
Marllon Nasser

Reputation: 390

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...

Upvotes: 1

Views: 709

Answers (2)

Carlo
Carlo

Reputation: 1579

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You want to count the number of hours since the earliest date/time for each record. Here is one approach:

select trunc(dt), count(distinct trunc((dt - min_dt) / 24)) as num_hours
from (select t.*, min(dt) over (partition by trunc(dt) order by dt) as min_dt
      from t
     ) t
group by trunc(dt);

This expression (dt - min_dt) / 24 calculates the time between each date/time and the earlier in hours. the count(distinct) counts the number of distinct hours seen in the date.

Upvotes: 0

Related Questions