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

Asked

Viewed 2,106 times

0

The rule

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

Dice

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? Why from the earliest date, records that are "within" up to one hour after that date, count as 1 only. More or less that:

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? Not with interactivity (for/while). Because I can have N dates in the middle, I don’t have a "standard" of minimum and maximum dates... the only thing I know is that the dates are within the same day.

I don’t want to have to make N selects to count between hour and hour...

sql oracle

  • I think at least you will have to adopt the standard hours of 00:00 M:S to 59:59 Minutes:Seconds. in your example you have 09:20:35 - 10:20:35, it becomes very variable since there is no beginning or end to start/finish.

  • "There’s no" and there’s at the same time... The point is to always take the smallest date and the biggest... and come up with a logic to do everything in the same query rs

  • you mean in that case the shortest date is 9:20:35 and counts 1 hour after that to the end, and then searches the next hour and counts another hour to the end and so on.

  • Yes... my shortest date can be at 07:15:00, for example...is always 1 hour from it...until the longest date. got it?

  • I think you could use something like that. The numbers are meaningless and need to be adjusted to your reality. select min( id ), case when ( date >= getdate() - 20 or data <= getdate() - 30 ) then 'yesterday' when ( date >= getdate() or date <= getdate() - 4 ) then 'today' when ( date >= getdate() + 12 or date <= getdate() - 20 ) then 'tomorrow' end as data from test group by data

  • @Marllonnasser, got it , you need to show the total and the intervals of hours?

  • I need one total per DAY. In short lines, for each interval I have record, I increment my counter.

  • THE BASE WOULD BE SOMETHING LIKE SELECT TO_CHAR(SYSDATE,'DD/MM/YY HH24:MI:SS') FULL, TO_CHAR(TRUNC(SYSDATE,'HH24'),'DD/MM/YY HH24:MI:SS') TRUNCATED FROM DUAL FULL TRUNCATED ---------------------------------------------------------------------------

Show 3 more comments

2 answers

0

Look at it this way:

SELECT * FROM TESTE

inserir a descrição da imagem aqui

SELECT  COUNT(*),  MIN( DATA ) FROM TESTE
GROUP BY ( DAY( DATA ) * 100000000 ) + (  MONTH( DATA ) * 1000000 ) +  YEAR(DATA  )

inserir a descrição da imagem aqui

That’s not what was expected, according to the question?

  • No, buddy.... Look at this: the expected is something like: 5 06/07/2017

  • And it wasn’t what returned in the first line?

  • Yes, but you used a different data case than mine. Based on yours, I would have to have a result of 1 2016-07-06, because the records are within an hour interval, so I count them all as one.

  • Hmmm. Sorry there. I closed for the day. In fact closing by the hour in my case of data would have to come back 12 because they are all contained at the same time, but of different days. Closing per hour would have to be something like this: SELECT COUNT(*), MIN( DATA ) FROM TEST GROUP BY DATEPART( hour, date ) + ( DATEPART( minute, date ) * 60 ) , since the select is restricted to one day. Otherwise I’d have to put the day in group by too.

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)

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.