This discussion is archived
9 Replies Latest reply: Mar 15, 2013 12:30 AM by 689466 RSS

select records closest to 15 minute time intervals

689466 Newbie
Currently Being Moderated
Hello,

I am trying to figure out a way to select records from a log table at 15 minute intervals (approx) based on a timestamp value in the table. The table has the following columns RECORD_ID, EVENT_TIME, EVENT_VALUE. Id like to select the records that are closest to 15 minute intervals for the last 24 hours starting at midnight.

So if there were records with the following values:

1, 13/03/2013 00:13:00, 100
2, 13/03/2013 00:16:00, 200
3, 13/03/2013 00:18:00, 300
4, 13/03/2013 00:28:00, 400
5, 13/03/2013 00:31:00, 500
6, 13/03/2013 00:41:00, 600
7, 13/03/2013 00:48:00, 700

The query should return
1, 13/03/2013 00:13:00, 100 -- Closest to 00:00
2, 13/03/2013 00:16:00, 200 -- Closest to 00:15
5, 13/03/2013 00:31:00, 500 -- Closest to 00:30
7, 13/03/2013 00:48:00, 700 -- Closest to 00:45

Any help is greatly appreciated. I've been going around in circles with this all day!!!
  • 1. Re: select records closest to 15 minute time intervals
    rp0428 Guru
    Currently Being Moderated
    >
    I am trying to figure out a way to select records from a log table at 15 minute intervals (approx) based on a timestamp value in the table. The table has the following columns RECORD_ID, EVENT_TIME, EVENT_VALUE. Id like to select the records that are closest to 15 minute intervals for the last 24 hours starting at midnight.
    >
    You need to fill in the missing information about the requirements.

    A single record could be the 'closest' to two 15 minute periods.

    For example if you remove the 16 minute record then the 13 minute record is closer than 18 minutes to the 15 minute interval but you said it is also closest to 0 minutes.

    Also there could be more than one record that is 'closest': both lower than the next interval or one 1 minute lower and the other 1 minute higher.

    Can you provide sample data for all of the boundary conditions and indicate how the selection should be made?
  • 2. Re: select records closest to 15 minute time intervals
    jeneesh Guru
    Currently Being Moderated
    This can be a starting point..
    SQL> with t as
      2  (
      3  select 1 event_id, to_date('13/03/2013 00:13:00','dd/mm/yyyy hh24:mi:ss') event_time, 100 event_value
      4  from dual union all
      5  select 2, to_date('13/03/2013 10:16:00','dd/mm/yyyy hh24:mi:ss'), 200
      6  from dual union all
      7  select 3, to_date('13/03/2013 00:18:00','dd/mm/yyyy hh24:mi:ss'), 300
      8  from dual
      9  ),
     10  t_rounded as
     11  (
     12  select event_id,event_time,event_value,
     13     trunc(event_time,'dd') + round(to_char(event_time,'sssss') / 900) / 96 r_time
     14  from t
     15  )
     16  select event_id,event_time,event_value
     17  from
     18  (
     19     select event_id,event_time,event_value,
     20           rank() over(partition by r_time
     21             order by abs(event_time-r_time)) rn
     22     from t_rounded
     23  )
     24  where rn = 1;
    
      EVENT_ID EVENT_TIME           EVENT_VALUE
    ---------- -------------------- -----------
             1 13-mar-2013 00:13:00         100
             2 13-mar-2013 10:16:00         200
  • 3. Re: select records closest to 15 minute time intervals
    jeneesh Guru
    Currently Being Moderated
    Or to get your output..
    SQL> with t as
      2  (
      3  select 1 event_id, to_date('13/03/2013 00:13:00','dd/mm/yyyy hh24:mi:ss') event_time, 100 event_value
      4  from dual union all
      5  select 2, to_date('13/03/2013 00:16:00','dd/mm/yyyy hh24:mi:ss'), 200
      6  from dual union all
      7  select 3, to_date('13/03/2013 00:18:00','dd/mm/yyyy hh24:mi:ss'), 300
      8  from dual union all
      9  select 4, to_date('13/03/2013 00:28:00','dd/mm/yyyy hh24:mi:ss'), 400
     10  from dual union all
     11  select 5, to_date('13/03/2013 00:31:00','dd/mm/yyyy hh24:mi:ss'), 500
     12  from dual union all
     13  select 6, to_date('13/03/2013 00:41:00','dd/mm/yyyy hh24:mi:ss'), 600
     14  from dual union all
     15  select 7, to_date('13/03/2013 00:48:00','dd/mm/yyyy hh24:mi:ss'), 700
     16  from dual
     17  ),
     18  t_rounded as
     19  (
     20  select event_id,event_time,event_value,
     21     trunc(event_time,'dd') + floor(to_char(event_time,'sssss') / 900) / 96 r_time
     22  from t
     23  )
     24  select event_id,event_time,event_value
     25  from
     26  (
     27     select event_id,event_time,event_value,
     28           rank() over(partition by r_time
     29             order by abs(event_time-r_time)) rn
     30     from t_rounded
     31  )
     32  where rn = 1
     33  order by event_time;
    
      EVENT_ID EVENT_TIME           EVENT_VALUE
    ---------- -------------------- -----------
             1 13-mar-2013 00:13:00         100
             2 13-mar-2013 00:16:00         200
             5 13-mar-2013 00:31:00         500
             7 13-mar-2013 00:48:00         700
  • 4. Re: select records closest to 15 minute time intervals
    689466 Newbie
    Currently Being Moderated
    Thank you for your response.

    In reality the data will have the same record_id recorded at many different times. The idea is to chose 4 records for each record_id per hour using the 15 interval as a guide. It its ok to select the same record twice if it is closest to two 15 minute periods. In the event of multiple records being equidistant the most recent can be chosen.

    Therefore for data:

    1, 13/03/2013 00:13:00, 100
    1, 13/03/2013 00:18:00, 200
    1, 13/03/2013 00:41:00, 300
    1, 13/03/2013 00:51:00, 400

    Should return the 4 records (per hour) deemed closest to the intervals.

    1, 13/03/2013 00:13:00, 100 -- Closest to 00:00
    1, 13/03/2013 00:18:00, 200 -- Equidistant from 00:15 as 00:13 but more recent
    1, 13/03/2013 00:41:00, 300 -- Closest to 00:30
    1, 13/03/2013 00:41:00, 300 -- Closest to 00:45

    Thanks - J
  • 5. Re: select records closest to 15 minute time intervals
    chris227 Guru
    Currently Being Moderated
    with data as  (
      select 1 event_id, to_date('13/03/2013 00:13:00','dd/mm/yyyy hh24:mi:ss') event_time, 100 event_value
      from dual union all
      select 2, to_date('13/03/2013 00:16:00','dd/mm/yyyy hh24:mi:ss'), 200
      from dual union all
      select 3, to_date('13/03/2013 00:18:00','dd/mm/yyyy hh24:mi:ss'), 300
      from dual union all
      select 4, to_date('13/03/2013 00:28:00','dd/mm/yyyy hh24:mi:ss'), 400
      from dual union all
      select 5, to_date('13/03/2013 00:31:00','dd/mm/yyyy hh24:mi:ss'), 500
      from dual union all
      select 6, to_date('13/03/2013 00:41:00','dd/mm/yyyy hh24:mi:ss'), 600
      from dual union all
      select 7, to_date('13/03/2013 00:48:00','dd/mm/yyyy hh24:mi:ss'), 700
      from dual
    )
    , ranking as (
    select
       event_id
      ,event_time
      ,event_value
      ,row_number() over (partition by 
                                floor(to_number(to_char(event_time, 'SSSSS'))/900)
                          order by
                          mod( extract(MINUTE from (cast(event_time as timestamp))), 15)
                          ) rnk
     from data
    )
    select
       event_id
      ,event_time
      ,event_value
    from ranking
    where
    rnk = 1
    
    EVENT_ID     EVENT_TIME     EVENT_VALUE
    "1"     13/03/2013 00:13:00     "100"
    "2"     13/03/2013 00:16:00     "200"
    "5"     13/03/2013 00:31:00     "500"
    "7"     13/03/2013 00:48:00     "700"
    regarding event id add a partition
    , ranking as (
    select
       event_id
      ,event_time
      ,event_value
      ,row_number() over (partition by
                                 event_id
                                ,floor(to_number(to_char(event_time, 'SSSSS'))/900)
                          order by
                          mod( extract(MINUTE from (cast(event_time as timestamp))), 15)
                          ) rnk
     from data
    )
    This assumes that there is a value in every quarter, otherwise nothing is displayed.
    There is a mistake in the data of your last example.
    There is 51 in the data but 41 in the result.
    If 41 should in fact be considered as the nearest for both quaters, we have to alter the solution.

    Edited by: chris227 on 13.03.2013 02:46
  • 6. Re: select records closest to 15 minute time intervals
    689466 Newbie
    Currently Being Moderated
    Thanks everyone.

    I will need to do some research on the suggested method and test against some real table data in order to understand the suggested solutions fully.
  • 7. Re: select records closest to 15 minute time intervals
    chris227 Guru
    Currently Being Moderated
    The solutions given yet wont give you the desired result, but they hint you to some tools.
    Before going further to find a correct solution, some more information is needed.
    For example it would be very helpful to know if there is at least one record for every quarter.
    Otherwise it would be necesary to find the nearest record over a range of unknown quarters, that will lead to a somehow different solution.
    Another question is, if there is always data of one day in the table or a separate calculation for every day is needed.

    Regards
  • 8. Re: select records closest to 15 minute time intervals
    689466 Newbie
    Currently Being Moderated
    Ok, there will not always be a record for each Event_ID in each quarter and the table data will span over years. I will be restricting the search to a set period (e.g. last 2 hours worth of data). The idea is to return a record at a given sampling interval (15 minutes in this case) for each event_id for each hour. (Lets assume there are no duplicates and that the most recent record will be chosen in the event of equidistant records)

    Ideally we would get 4 records per event_id for each hour however if only 1 record exists for a given hour for an event_id then we want that record returned once (matched against its nearest quarter). It should not be ignored it because it doesn't have entries in other quarters.

    My first attempt is below as I'm still trying to understand the above solutions.

    My approach (so far) is to calculate a target quarter for each record and then calculate the time difference from each record to its target quarter. I then return the record with the smallest time difference per event_id/quarter grouping. This should produce a minimum of 1 record for each Event_Id entry however will only return 1 record per event_id/quarter grouping (not ideal).

    Obvious Limitations:
    1. A record will only be considered for retrieval within a specific quarter. i.e. If there is only 2 records for the hour but both are in the same quarter only 1 is returned.
    2. Im not 100% if the minimum difference is always the correct (when the difference is negative)
    3. It seems to take a long time to process

    Any comments or suggestions welcome :)
    Select event_data.* from 
    (   SELECT elog.event_id event_id,elog.event_value event_value, event_time, 
              EXTRACT (MINUTE FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))*60 +
                 EXTRACT (SECOND FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time)) delta,
              to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) target
        FROM sc_event_log elog 
        WHERE elog.event_time  > (CURRENT_TIMESTAMP - 0.08055555555)
    ) event_data  
    JOIN
    (select t.event_id, t.target, min(t.delta) mindelta from
       (  SELECT elog.event_id event_id,elog.event_value event_value, event_time, 
              EXTRACT (MINUTE FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))*60 +
                 EXTRACT (SECOND FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time)) delta,
              to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss') target
          FROM sc_event_log elog 
          WHERE elog.event_time  > (CURRENT_TIMESTAMP - 0.08055555555
       )
    ) t
    group by t.event_id, t.target) match
    ON event_data.event_id=match.event_id
    AND event_data.delta = match.mindelta
    AND event_data.target = match.target
    ORDER BY event_data.event_id,event_data.TARGET,event_data.EVENT_TIME
  • 9. Re: select records closest to 15 minute time intervals
    689466 Newbie
    Currently Being Moderated
    I tried to restructure my query above using the analytic function example and came up with this.......I'm not 100% but I think its working correctly!!
    SELECT event_data.event_id, event_data.event_time, event_data.event_value
    from (
       SELECT t.event_id, t.target, t.delta, t.event_time,t.event_value,  rank()
       OVER (partition by t.target,t.event_id order by  t.event_id, t.target,t.delta) rank
       FROM
       (  SELECT elog.event_id event_id,elog.event_value event_value, event_time, 
             abs( EXTRACT (MINUTE FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))*60 +
                 EXTRACT (SECOND FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))) delta,
              to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) target
          FROM event_log elog
          WHERE elog.event_time  > (CURRENT_TIMESTAMP - 0.08055555555) 
       ) t
    ) event_data WHERE rank = 1
    order by event_data.event_id, event_data.event_time, event_data.event_value
    Edited by: jsha on Mar 15, 2013 12:28 AM (added abs function to delta column calculation to allow correct ranking for negative time differences)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points