Search code examples
sqloracle-databaseconnect-by

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

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
 )
Solution

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