1 2 Previous Next 22 Replies Latest reply: Nov 8, 2008 6:59 AM by Satyaki_De RSS

    Select records at interval of 5 minutes

    645384
      hi, I've asked this question before, but it's not solved yet. Thanks a lot for all your suggestion and help!

      The data is like this:


      TYPE | ID | TIME
      CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
      CHIT|1000311370|11-JUN-08 05.40.10.000000000 AM
      CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
      CHIT|9050|11-JUN-08 05.49.41.000000000 AM
      CHIT|9050|11-JUN-08 05.50.41.000000000 AM
      CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
      CMIK|5480|11-JUN-08 05.54.30.000000000 AM
      CHIT|1000005980|11-JUN-08 05.54.36.000000000 AM
      CMIK|1000289540|11-JUN-08 05.58.10.000000000 AM
      CMIK|5480|11-JUN-08 06.01.03.000000000 AM
      CMIK|1000094670|11-JUN-08 06.01.03.000000000 AM
      CMIK|1000095130|11-JUN-08 06.04.42.000000000 AM
      CMIK|5480|11-JUN-08 06.04.42.000000000 AM
      CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
      EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
      CMIK|9040|11-JUN-08 06.25.44.000000000 AM
      CHIT|1000311390|11-JUN-08 06.25.50.000000000 AM
      CHIT|5480|11-JUN-08 06.25.50.000000000 AM
      CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
      EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
      CMIK|5480|11-JUN-08 06.51.42.000000000 AM
      CMIK|1000251580|11-JUN-08 06.51.42.000000000 AM
      CMIK|5480|11-JUN-08 06.58.12.000000000 AM
      CMIK|1000251580|11-JUN-08 06.58.12.000000000 AM
      EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
      EHUY|100663396|11-JUN-08 07.09.50.000000000 AM
      EHUY|100663418|11-JUN-08 07.09.50.000000000 AM
      CMIK|5480|11-JUN-08 07.27.27.000000000 AM
      CMIK|1000094670|11-JUN-08 07.27.27.000000000 AM
      CMIK|1000289540|11-JUN-08 07.30.28.000000000 AM

      The requirement:
      Starting from the 1st record, skip for 5 minutes (or more, if we could not find one record at exact 5 mintutes point), then choose the next one; then skip for another 5 minutes (or more) and so on. Putting it in another way, we'll ignore records within 5 minutes time range.

      So, the result from the above data should be:

      TYPE | ID | TIME
      CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
      CMIK|1000289540|11-JUN-08 05.58.10.000000000 AM
      EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
      CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
      CMIK|5480|11-JUN-08 06.58.12.000000000 AM
      CMIK|5480|11-JUN-08 07.27.27.000000000 AM
        • 1. Re: Select records at interval of 5 minutes
          NicloeiW
          like this
          --http://forums.oracle.com/forums/message.jspa?messageID=2593239#2593239
          with t As
          (

          Select 'CHIT' c1  , 1000311390  c2  , to_date('11-JUN-08 05.39.59 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 1000311370   , to_date('11-JUN-08 05.40.10 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 1000311370   , to_date('11-JUN-08 05.46.27 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 9050         , to_date('11-JUN-08 05.49.41 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 9050         , to_date('11-JUN-08 05.50.41 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000094670   , to_date('11-JUN-08 05.54.30 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 5480         , to_date('11-JUN-08 05.54.30 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 1000005980   , to_date('11-JUN-08 05.54.36 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000289540   , to_date('11-JUN-08 05.58.10 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 5480         , to_date('11-JUN-08 06.01.03 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000094670   , to_date('11-JUN-08 06.01.03 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000095130   , to_date('11-JUN-08 06.04.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 5480         , to_date('11-JUN-08 06.04.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000056490   , to_date('11-JUN-08 06.11.18 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'EHUY'   , 486604824    , to_date('11-JUN-08 06.21.55 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 9040         , to_date('11-JUN-08 06.25.44 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 1000311390   , to_date('11-JUN-08 06.25.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 5480         , to_date('11-JUN-08 06.25.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CHIT'   , 1000016600   , to_date('11-JUN-08 06.37.53 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'EHUY'   , 492437505    , to_date('11-JUN-08 06.50.20 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 5480         , to_date('11-JUN-08 06.51.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000251580   , to_date('11-JUN-08 06.51.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 5480         , to_date('11-JUN-08 06.58.12 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000251580   , to_date('11-JUN-08 06.58.12 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'EHUY'   , 134414363   , to_date('11-JUN-08 07.09.38 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'EHUY'  , 100663396    , to_date('11-JUN-08 07.09.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'EHUY'  , 100663418    , to_date('11-JUN-08 07.09.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 5480        , to_date('11-JUN-08 07.27.27 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000094670   , to_date('11-JUN-08 07.27.27 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
          Select 'CMIK'   , 1000289540   , to_date('11-JUN-08 07.30.28 AM','dd-mon-rr hh:mi:ss AM') c3 From dual
          )
          Select c1, c2, c3 From
          (
          Select c1, c2, c3,
                   first_value(c3) over(Order By c3 Range Between Interval '5' Minute PRECEDING AND CURRENT ROW) new_c3
          From t
          )
          Where c3 = new_c3

                  C1     C2     C3
          1     CHIT     1000311390     6/11/2008 5:39:59 AM
          2     CHIT     1000311370     6/11/2008 5:46:27 AM
          3     CMIK     1000056490     6/11/2008 6:11:18 AM
          4     EHUY     486604824     6/11/2008 6:21:55 AM
          5     CHIT     1000016600     6/11/2008 6:37:53 AM
          6     EHUY     492437505     6/11/2008 6:50:20 AM
          7     CMIK     5480     6/11/2008 6:58:12 AM
          8     CMIK     1000251580     6/11/2008 6:58:12 AM
          9     EHUY     134414363     6/11/2008 7:09:38 AM
          10     CMIK     5480     6/11/2008 7:27:27 AM
          11     CMIK     1000094670     6/11/2008 7:27:27 AM
          • 2. Re: Select records at interval of 5 minutes
            645384
            hi, Nicolei,
            thanks a lot for your quick response. But there are a few problems.

            I gave the result in the last post. But it was based on 15 minutes.
            If calculated for 5 minutes interval, the result should be this:

            CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
            CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
            CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
            CMIK|5480|11-JUN-08 06.01.03.000000000 AM
            CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
            EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
            CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
            EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
            CMIK|5480|11-JUN-08 06.58.12.000000000 AM
            EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
            CMIK|5480|11-JUN-08 07.27.27.000000000 AM



            Your result is incorrect,
            First,
            the 7th (6/11/2008 6:58:12 ) and 8th (6/11/2008 6:58:12 ) are in a tie;
            and so are the same for 10th and 11th. That is, the interval is 0 instead
            of "5 minutes".
            I think this is a bug.
            So how to eliminate tie records,(only keep one of the tie records)?

            I think you're almost there. I'm going to study your solution carefully. Thank you so much!!!
            • 3. Re: Select records at interval of 5 minutes
              NicloeiW
              i am not getting you, kindly post it properly for more people to answer you,

              else you wont get nice answers
              • 4. Re: Select records at interval of 5 minutes
                Aketi Jyuuzou
                I have thought arranged question.
                Umm I recommend to use TableFunction or model.

                10 <-- will Output
                14
                15 <-- will Output
                30 <-- will Output
                36 <-- will Output
                40
                45 <-- will Output
                50 <-- will Output
                51
                52
                53
                54
                55 <-- will Output
                61 <-- will Output
                65
                66 <-- will Output
                67
                • 5. Re: Select records at interval of 5 minutes
                  645384
                  we start from the 1st record, that's

                  CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM

                  then we skip for 5 minutes, the time should be 05:44:59 am (or later), but there is no record at this time point, so we simply move to the next record that is later than 05:44:59, that's

                  CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM

                  Now, we skip for 5 minutes again, the time should be 05:51:27 am (or later), however, there is no record at this time point, so we move on to the next record, that's

                  CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM

                  skip for another 5 minutes, the time should be 05:59:30 am, since there is no record at this time point, we move on to the next one which is later than 05:59:30 am. That is:

                  CMIK|5480|11-JUN-08 06.01.03.000000000 AM

                  skip for 5 minutes again, next record should be at 06:06:03 am, but no records at this time, so move on, and choose:

                  CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM

                  skip for 5 minutes, next record should be at 06:16:18 am or later, so we choose;

                  EHUY|486604824|11-JUN-08 06.21.55.000000000 AM

                  for another 5 minutes, next record should be at 06:26:55 am( or later), so we move to this one:

                  CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM

                  5 minutes later, time should be 06:42:53 am, but no records at this time, so simply move on to the next one:

                  EHUY|492437505|11-JUN-08 06.50.20.000000000 AM

                  now, after 5 minutes, we'll choose a record at 06:55:20 am, no records at this time point, so we move to the closest record to this time, that's:

                  CMIK|5480|11-JUN-08 06.58.12.000000000 AM

                  next one should at 07:03:12 am (or later), so we'll actually choose:

                  EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
                  after 5 minutes, it's 07:14:38 am, so we'll choose

                  CMIK|5480|11-JUN-08 07.27.27.000000000 AM


                  So the result should be this:

                  CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
                  CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
                  CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
                  CMIK|5480|11-JUN-08 06.01.03.000000000 AM
                  CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
                  EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
                  CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
                  EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
                  CMIK|5480|11-JUN-08 06.58.12.000000000 AM
                  EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
                  CMIK|5480|11-JUN-08 07.27.27.000000000 AM


                  You can see from the result, each record is at least 5 minutes apart from it's previous record.

                  Hope this clarification makes sense.

                  Thanks a lot for all your help!
                  • 6. Re: Select records at interval of 5 minutes
                    645384
                    I could not understand your "thought", what are the numbers? Looks like line numbers but I don't have over 30 lines of data.

                    Let me breiefly explain the requirement here:

                    choose the the 1st record, then skip for 5 minutes, if no record at exact 5 minutes point, then simply move to the next record which is closest to 5 minutes;

                    Then wait for another 5 minutes, and so on.

                    So in the result, each record should be at least 5 minutes later than its previous record.
                    here it is:

                    CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
                    CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
                    CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
                    CMIK|5480|11-JUN-08 06.01.03.000000000 AM
                    CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
                    EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
                    CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
                    EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
                    CMIK|5480|11-JUN-08 06.58.12.000000000 AM
                    EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
                    CMIK|5480|11-JUN-08 07.27.27.000000000 AM
                    • 7. Re: Select records at interval of 5 minutes
                      561825
                      Check this link.

                      Query to calculate TV Events ( Agregate function?)

                      Your requirement is more or less similar to this one.

                      Regards

                      Raj
                      • 8. Re: Select records at interval of 5 minutes
                        603036
                        if Nic's result set was essentially correct, but only has duplicates (tie records) just use the row_number() analytic function and wrap it with another select to grab only the first row:
                        SELECT c1, c2, c3
                          FROM (SELECT c1, c2, c3,
                                       ROW_NUMBER () OVER (PARTITION BY c1, c3 ORDER BY c2 DESC) rn
                                  FROM (SELECT c1, c2, c3,
                                               FIRST_VALUE (c3) OVER (ORDER BY c3 RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW)
                                                                                               new_c3
                                          FROM t)
                                 WHERE c3 = new_c3)
                        WHERE rn = 1;
                        -- uses Nic's 't' table.

                        Message was edited by:
                        RACER

                        left out some of Nic's code...
                        • 9. Re: Select records at interval of 5 minutes
                          645384
                          hi,

                          Nicloei's result is not correct. Two problems,
                          1, there are duplicate records.
                          2, he skipped some records (which should be included in the result); and then he included some records which supposed not to be there.
                          His first two records are correct;

                          comparing with the result I want:

                          CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
                          CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
                          CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
                          CMIK|5480|11-JUN-08 06.01.03.000000000 AM
                          CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
                          EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
                          CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
                          EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
                          CMIK|5480|11-JUN-08 06.58.12.000000000 AM
                          EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
                          CMIK|5480|11-JUN-08 07.27.27.000000000 AM
                          • 10. Re: Select records at interval of 5 minutes
                            NicloeiW
                            then probably wait for other experts to look at your post
                            • 11. Re: Select records at interval of 5 minutes
                              Aketi Jyuuzou
                              I could not understand your "thought", what are the numbers? Looks like line numbers but I don't have over 30 lines of data.
                              I have thought arranged question.

                              When we make difficult query,
                              it is very important that we think easy arranged question.

                              Then I solved arranged question.
                              AND then I solved your question.
                              create table treeValue(Val) as
                              select 10 from dual union
                              select 14 from dual union
                              select 15 from dual union
                              select 30 from dual union
                              select 36 from dual union
                              select 40 from dual union
                              select 45 from dual union
                              select 50 from dual union
                              select 51 from dual union
                              select 52 from dual union
                              select 53 from dual union
                              select 54 from dual union
                              select 55 from dual union
                              select 61 from dual union
                              select 65 from dual union
                              select 66 from dual union
                              select 67 from dual;
                              
                              select Val
                              from (select Val,min(Val) over() as StartVal,
                                    min(Val) over(order by Val
                                                  range between 5 following
                                                    and unbounded following) as childVal
                                      from treeValue)
                              Start With Val = StartVal
                              connect by prior childVal = Val;
                              
                              VAL
                              ---
                               10
                               15
                               30
                               36
                               45
                               50
                               55
                               61
                               66
                              • 12. Re: Select records at interval of 5 minutes
                                Aketi Jyuuzou
                                I used First_value which is Posted by "Jane Doe".
                                select based on column value
                                create table TimeTree(ID,timeValue) as
                                Select  1,TimeStamp '2008-06-11 05:39:59' From dual Union
                                Select  2,TimeStamp '2008-06-11 05:40:10' From dual Union
                                Select  3,TimeStamp '2008-06-11 05:46:27' From dual Union
                                Select  4,TimeStamp '2008-06-11 05:49:41' From dual Union
                                Select  5,TimeStamp '2008-06-11 05:50:41' From dual Union
                                Select  6,TimeStamp '2008-06-11 05:54:30' From dual Union
                                Select  7,TimeStamp '2008-06-11 05:54:30' From dual Union
                                Select  8,TimeStamp '2008-06-11 05:54:36' From dual Union
                                Select  9,TimeStamp '2008-06-11 05:58:10' From dual Union
                                Select 10,TimeStamp '2008-06-11 06:01:03' From dual Union
                                Select 11,TimeStamp '2008-06-11 06:01:03' From dual Union
                                Select 12,TimeStamp '2008-06-11 06:04:42' From dual Union
                                Select 13,TimeStamp '2008-06-11 06:04:42' From dual Union
                                Select 14,TimeStamp '2008-06-11 06:11:18' From dual Union
                                Select 15,TimeStamp '2008-06-11 06:21:55' From dual Union
                                Select 16,TimeStamp '2008-06-11 06:25:44' From dual Union
                                Select 17,TimeStamp '2008-06-11 06:25:50' From dual Union
                                Select 18,TimeStamp '2008-06-11 06:25:50' From dual Union
                                Select 19,TimeStamp '2008-06-11 06:37:53' From dual Union
                                Select 20,TimeStamp '2008-06-11 06:50:20' From dual Union
                                Select 21,TimeStamp '2008-06-11 06:51:42' From dual Union
                                Select 22,TimeStamp '2008-06-11 06:51:42' From dual Union
                                Select 23,TimeStamp '2008-06-11 06:58:12' From dual Union
                                Select 24,TimeStamp '2008-06-11 06:58:12' From dual Union
                                Select 25,TimeStamp '2008-06-11 07:09:38' From dual Union
                                Select 26,TimeStamp '2008-06-11 07:09:50' From dual Union
                                Select 27,TimeStamp '2008-06-11 07:09:50' From dual Union
                                Select 28,TimeStamp '2008-06-11 07:27:27' From dual Union
                                Select 29,TimeStamp '2008-06-11 07:27:27' From dual Union
                                Select 30,TimeStamp '2008-06-11 07:30:28' From dual;
                                
                                with WorkView as (
                                Select ID,timeValue,RowID as Row_ID,
                                min(timeValue) over() as startTimeValue,
                                first_value(RowID) over(order by timeValue
                                                        range between INTERVAL '5' MINUTE following
                                                          and unbounded following) as childRowID
                                  from TimeTree)
                                select ID,to_char(timeValue, 'YYYY/MM/DD HH24:MI:SS') as timeValue
                                from WorkView
                                Start With timeValue = startTimeValue
                                connect by prior childRowID = Row_ID;
                                
                                ID  TIMEVALUE
                                --  -------------------
                                 1  2008/06/11 05:39:59
                                 3  2008/06/11 05:46:27
                                 6  2008/06/11 05:54:30
                                10  2008/06/11 06:01:03
                                14  2008/06/11 06:11:18
                                15  2008/06/11 06:21:55
                                19  2008/06/11 06:37:53
                                20  2008/06/11 06:50:20
                                23  2008/06/11 06:58:12
                                25  2008/06/11 07:09:38
                                28  2008/06/11 07:27:27
                                -------------------------------------------------------------------------------

                                I executed below query but I got syntax error.
                                select ID,to_char(timeValue, 'YYYY/MM/DD HH24:MI:SS') as timeValue
                                from (Select ID,timeValue,RowID as Row_ID,
                                      min(timeValue) over() as startTimeValue,
                                      first_value(RowID) over(order by timeValue
                                                              range between INTERVAL '5' MINUTE following
                                                                and unbounded following) as childRowID
                                        from TimeTree)
                                Start With timeValue = startTimeValue
                                connect by prior childRowID = Row_ID;
                                
                                                              range between INTERVAL '5' MINUTE following
                                                                        *
                                ORA-00905: missing keyword
                                
                                SQL> select version from v$instance;
                                
                                VERSION
                                -----------------
                                10.1.0.2.0
                                Therefore I used "with clause".
                                However,I do not know why "with clause" prevents syntax error.
                                • 13. Re: Select records at interval of 5 minutes
                                  NicloeiW
                                  hey, its working for me
                                  SQL> select *
                                    2  from v$version;

                                  BANNER
                                  ----------------------------------------------------------------
                                  Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
                                  PL/SQL Release 10.2.0.3.0 - Production
                                  CORE     10.2.0.3.0     Production

                                  TNS for Solaris: Version 10.2.0.3.0 - Production
                                  NLSRTL Version 10.2.0.3.0 - Production

                                  SQL>
                                  SQL> select ID,to_char(timeValue, 'YYYY/MM/DD HH24:MI:SS') as timeValue
                                    2  from (Select ID,timeValue,RowID as Row_ID,min(timeValue) over() as startTimeValue,
                                    3        first_value(RowID) over(order by timeValue
                                    4                                range between INTERVAL '5' MINUTE following
                                    5                                  and unbounded following) as childRowID
                                    6          from TimeTree)
                                    7  Start With timeValue = startTimeValue
                                    8  connect by prior childRowID = Row_ID;

                                          ID TIMEVALUE
                                  ---------- -------------------
                                           1 2008/06/11 05:39:59
                                           3 2008/06/11 05:46:27
                                           6 2008/06/11 05:54:30
                                          10 2008/06/11 06:01:03
                                          14 2008/06/11 06:11:18
                                          15 2008/06/11 06:21:55
                                          19 2008/06/11 06:37:53
                                          20 2008/06/11 06:50:20
                                          23 2008/06/11 06:58:12
                                          25 2008/06/11 07:09:38
                                          28 2008/06/11 07:27:27

                                  11 rows selected

                                  SQL>
                                  • 14. Re: Select records at interval of 5 minutes
                                    611118
                                    Well, that was fun. I just came up with the same result as you. :)
                                    WITH
                                         TimeTree
                                    AS
                                         (
                                          SELECT  1 Id, TO_DATE('11-06-08 05.39.59','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  2 Id, TO_DATE('11-06-08 05.40.10','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  3 Id, TO_DATE('11-06-08 05.46.27','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  4 Id, TO_DATE('11-06-08 05.49.41','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  5 Id, TO_DATE('11-06-08 05.50.41','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  6 Id, TO_DATE('11-06-08 05.54.30','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  7 Id, TO_DATE('11-06-08 05.54.30','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  8 Id, TO_DATE('11-06-08 05.54.36','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT  9 Id, TO_DATE('11-06-08 05.58.10','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 10 Id, TO_DATE('11-06-08 06.01.03','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 11 Id, TO_DATE('11-06-08 06.01.03','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 12 Id, TO_DATE('11-06-08 06.04.42','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 13 Id, TO_DATE('11-06-08 06.04.42','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 14 Id, TO_DATE('11-06-08 06.11.18','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 15 Id, TO_DATE('11-06-08 06.21.55','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 16 Id, TO_DATE('11-06-08 06.25.44','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 17 Id, TO_DATE('11-06-08 06.25.50','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 18 Id, TO_DATE('11-06-08 06.25.50','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 19 Id, TO_DATE('11-06-08 06.37.53','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 20 Id, TO_DATE('11-06-08 06.50.20','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 21 Id, TO_DATE('11-06-08 06.51.42','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 22 Id, TO_DATE('11-06-08 06.51.42','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 23 Id, TO_DATE('11-06-08 06.58.12','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 24 Id, TO_DATE('11-06-08 06.58.12','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 25 Id, TO_DATE('11-06-08 07.09.38','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 26 Id, TO_DATE('11-06-08 07.09.50','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 27 Id, TO_DATE('11-06-08 07.09.50','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 28 Id, TO_DATE('11-06-08 07.27.27','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 29 Id, TO_DATE('11-06-08 07.27.27','DD-MM-RR HH:MI:SS') Value FROM Dual UNION ALL
                                          SELECT 30 Id, TO_DATE('11-06-08 07.30.28','DD-MM-RR HH:MI:SS') Value FROM Dual
                                         )
                                    SELECT DISTINCT
                                         Value
                                    FROM
                                         (
                                          SELECT
                                              Value,
                                              FIRST_VALUE(Value)
                                               OVER
                                               (
                                                ORDER BY Value
                                                RANGE BETWEEN INTERVAL '15' MINUTE FOLLOWING AND UNBOUNDED FOLLOWING
                                               )               Next_Value
                                          FROM
                                              TimeTree
                                         )
                                    CONNECT BY
                                         Value = PRIOR Next_Value
                                    START WITH
                                         Value = TO_DATE('11-06-08 05.39.59','DD-MM-RR HH:MI:SS')
                                    ORDER BY
                                         Value;
                                    1 2 Previous Next