Select a row for each minute between Date Values in each row

Question

I have a table that has records in 5 minute increments that looks like the following:

 UDSNSI     TIMESTAMP
 -8134      7/20/2016 4:30:00 AM
 -8125      7/20/2016 4:35:00 AM
 -8098      7/20/2016 4:40:00 AM

I need to select from in such a way that would create a result for every minute up to the time in the next row from now to the last 5 hours. For example:

UDSNSI     TIMESTAMP

-8134      7/20/2016 4:30:00 AM
-8134      7/20/2016 4:31:00 AM
-8134      7/20/2016 4:32:00 AM
-8134      7/20/2016 4:33:00 AM
-8134      7/20/2016 4:34:00 AM
-8125      7/20/2016 4:35:00 AM
-8125      7/20/2016 4:36:00 AM
-8125      7/20/2016 4:37:00 AM
...

I feel like I can use "CONNECT BY" but I can't seem to figure out how to tell it to use the TIMESTAMP from one row as the start and the next row as the end each time.

 select udsnsi, 
        timestamp
 from plan
 where timestamp <sysdate and timestamp >= sysdate - 5/24

 connect by timestamp <=
 (
     select .... timestamp from row X and row X + 1
                 and create a row for every minute value in between
                 using X's udsnsi value
 )

If the existing rows are in fact as you said, five minutes apart, why do you need to compare to the next row? Or are they not really exactly five minutes apart, each and every time? — , Jul 20 '16 at 21:39
@mathguy They are exactly 5 minutes apart, I guess I didn't think about not really having to consider the row. I could just maybe take a time and add 4 minutes each time. — jrandomuser, Jul 21 '16 at 13:19

score 2 · Accepted Answer · 2016-07-21T14:04:29.437

Something like this should work. Note the use of the analytic function lead() to identify the "next" time. You shouldn't need it (and you then wouldn't need a separate subquery) if the intervals were in fact exactly five minutes each, since in that case you could simply say level <= 5. Also, I changed the column name from timestamp to timestp - using reserved Oracle keywords as column names is asking for trouble.

with
     plan (    udsnsi, timestp ) as (
       select '-8134', to_date('7/20/2016 4:30:00 PM', 'mm/dd/yyyy hh:mi:ss AM') 
                                                                       from dual union all
       select '-8125', to_date('7/20/2016 4:35:00 PM', 'mm/dd/yyyy hh:mi:ss AM') 
                                                                       from dual union all
       select '-8098', to_date('7/20/2016 4:40:00 PM', 'mm/dd/yyyy hh:mi:ss AM') 
                                                                                 from dual
     ),
     prep (   udsnsi, timestp, next_timestp ) as (
       select udsnsi, timestp, lead(timestp) over (order by timestp)
       from   plan
       where  timestp < sysdate and timestp >= sysdate - 5/24
     )
select udsnsi, timestp + (level - 1) / (24 * 60) as timestp
from   prep
connect by prior udsnsi = udsnsi
and        prior sys_guid() is not null
and        level <= (next_timestp - timestp) * (24 * 60)
order by timestp
;

UDSNSI TIMESTP
------ ----------------------
-8134  07/20/2016 04:30:00 PM
-8134  07/20/2016 04:31:00 PM
-8134  07/20/2016 04:32:00 PM
-8134  07/20/2016 04:33:00 PM
-8134  07/20/2016 04:34:00 PM
-8125  07/20/2016 04:35:00 PM
-8125  07/20/2016 04:36:00 PM
-8125  07/20/2016 04:37:00 PM
-8125  07/20/2016 04:38:00 PM
-8125  07/20/2016 04:39:00 PM
-8098  07/20/2016 04:40:00 PM

ADDED: the OP clarified that the intervals are always exactly five minutes apart. So the solution is much simpler:

select udsnsi, timestp + (level - 1) / (24 * 60) as timestp
from   prep
connect by prior udsnsi = udsnsi
  and      prior sys_guid() is not null
  and      level <= 5
;

One difference vs. the first solution: In this (second, simpler) solution, additional rows will also be added for the "last" row in the original table. It is not really clear what the requirement is for the very last row, whether these additional rows are needed or not.

This is a brilliant solution that took me a little time to get my head around. I implemented it both ways just so I would understand it. I did find an additional reason to go with level <= 5 instead of level <= (next_timestp - timestp) * (24 * 60). If you rely on the next timestamp record you won't be able to get the most recent results until the 5 minute mark as there is no record for it to compare to. Using <= 5 you will always get the most recent results. — jrandomuser, Jul 21 '16 at 21:11

Select a row for each minute between Date Values in each row

1 Answers1