Skip navigation.

Generate list of dates and times with set interval

Generic query generating a list of date time. You need to substitute required interval number.

select date_from+(level-1)/intervalPicked    as datetime2split
from (select from_dt as date_from
    ,to_dt           as date_to
    ,to_dt-from_dt+1 as days_between 
/* Select interval: 1->day, 48->30min, 96->15min, 144->10min, 288->5min */
    ,144             as intervalPicked   
from (select to_date('01-Jan-2011')         as from_dt
           , to_date('02-Jan-2011')-1/86400 as to_dt 
      from dual))
connect by (level-1) <= days_between*intervalPicked

USAGE EXAMPLE:

The modified query yields the result set below:

select  date_from+(level-1)/1    as "GenerateTimeBy1Day"
       ,date_from+(level-1)/48   as "GenerateTimeBy30min"
       ,date_from+(level-1)/96   as "GenerateTimeBy15min"
       ,date_from+(level-1)/144  as "GenerateTimeBy10min"
       ,date_from+(level-1)/288  as "GenerateTimeBy5min" 
from (select from_dt as date_from
    ,to_dt           as date_to
    ,to_dt-from_dt+1 as days_between 
    ,288             as intervalPicked   
from (select to_date('01-Jan-2011')         as from_dt
           , to_date('02-Jan-2011')-1/86400 as to_dt 
      from dual))
connect by (level-1) <= days_between*intervalPicked  

AttachmentSize
20110512_ResultSetExample.jpg119.98 KB

hi

Nice and helpful!