CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
user3022875
user3022875

Reputation: 9018

Query to generate a list of date times

I'd like to build a query that has some variables named:

    EndDate --this is a date
    LookBackDays --this is an integer
    Interval -- this is an interval in SECONDS
    Name -- this is  a string
   open -this is a string and is hours and minutes  in 24HH
   close - this is string and is hours and minutes in 24HH

let's say I set my varaibles to:

EndDate to 7/4
LookBackDays  = 3
Inteval = 300 seconds
Name = "BOB"
open = "09:00"
close = "13:00"

I then want to generate a list of dates from the Endate-LookbackDays to the EndDate that will look like

That will look like 

  Date     Open      Close       Name
7/1/2015   09:00 AM    13:00 PM    Bob
7/2/2015   09:00 AM    13:00 PM    Bob
7/3/2015   09:00 AM    13:00 PM    Bob
7/4/2015   09:00 AM    13:00 PM    Bob
....
....

Then for each Date I'd like to add additional rows using the Interval parameter where for each row I break up the time interval from the open time to the close time into buckets based on the interval so for an interval of 300 seconds the results would be:

   date          oepn                close             name    IntervalEnd
 7/1/2015    7/1/2015 09:00 AM   7/1/2015 13:00 PM    Bob        09:05 AM
 7/1/2015   7/1/2015 09:00 AM   7/1/2015 13:00 PM    Bob        09:10 AM
 7/1/2015  7/1/2015 09:00 AM   7/1/2015 13:00 PM    Bob        09:15 AM
 7/1/2015  7/1/2015 09:00 AM    7/1/2015 13:00 PM    Bob        09:20 AM
 7/1/2015  7/1/2015 09:00 AM   7/1/2015 13:00 PM    Bob        09:25 AM
....
....
 7/1/2015   09:00 AM   7/1/2015 13:00 PM    Bob       7/1/2015 12:55 PM
 7/1/2015   09:00 AM   7/1/2015 13:00 PM    Bob       7/1/2015 01:00 PM
 **7/2/2015**  7/2/2015 09:00 AM   7/2/2015 13:00 PM    Bob        09:05 AM
 7/2/2015  7/2/2015 09:00 AM   7/2/2015 13:00 PM    Bob        09:10 AM
 7/2/2015  7/2/2015 09:00 AM   7/2/2015 13:00 PM    Bob        09:15 AM
 7/2/2015  7/2/2015 09:00 AM   7/2/2015 13:00 PM    Bob        09:20 AM
 7/2/2015  7/2/2015 09:00 AM   7/2/2015 13:00 PM    Bob        09:25 AM
....
....
 7/2/2015  7/2/2015 09:00 AM   7/2/2015 13:00 PM    Bob        12:55 PM
 7/2/2015  7/2/2015 09:00 AM   7/2/2015 13:00 PM    Bob        01:00 PM

...and so on for each day. So basically I am taking each day and exapanding it into multiple buckets

Upvotes: 0

Views: 88

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You could cross join two hierarchical queries, like here:

with par as (
  select date '2015-07-04' enddate, 3 LookBackDays, 300 inteval, 'Bob' Name, 
         '09:00' open, '13:00' close from dual),
t1 as (
  select to_char(enddate-level+1, 'yyyy-mm-dd') dt, name, open, close from par
    connect by level <= LookBackDays + 1 ),
t2 as (
  select to_char(to_date(open, 'hh24:mi') + (level) * inteval / (24*60*60), 'hh24:mi') tm
    from par
    connect by to_date(open, 'hh24:mi') + level * inteval / (24*60*60) 
               <= to_date(close, 'hh24:mi') )
select to_date(dt, 'yyyy-mm-dd') dt, 
       to_date(dt||' '||open, 'yyyy-mm-dd hh24:mi') open, 
       to_date(dt||' '||close, 'yyyy-mm-dd hh24:mi') close, name, 
       to_date(dt||' '||tm, 'yyyy-mm-dd hh24:mi') IntervalEnd
  from t1 cross join t2 order by dt, tm

SQLFiddle demo

Upvotes: 2

Related Questions