TIME FUNCTIONS IN SQL

[mmasa]

Hi ORACLE Fans,
I have a problem, which is (I think) really complicated:

I have a table with 3 columns:
TIME_OPENED | TIME_CLOSED | DURATION
7/30/2001 10:30:51 | 7/30/2001 10:43:57 | 786
7/30/2001 10:38:19 | 7/30/2001 10:44:02 | 343

I want to generate a new table, in which per row new rows get generated as much as minutes in the TIME-Information are contained.
This means a table with the following row:

TIME
7/30/2001 10:30
7/30/2001 10:31
7/30/2001 10:32
and so on..

How can I do this with an SQL-Script?

Thanks
Michael

 

[yaffle]

You could do it with a PL/SQL script e.g.
[tt]
DECLARE
l_date date;
BEGIN
FOR c_duration IN
(select time_opened, time_closed
from duration)
LOOP
l_date := to_date(to_char(c_duration.time_opened,'YYYYMMDDHH24MI'),
'YYYYMMDDHH24MI');
LOOP
EXIT WHEN l_date > c_duration.time_closed;
insert into open_minutes(time)
values ();
l_date := l_date + 1/1440; -- 1440 minutes per day
END LOOP;
END;
[/tt]
Something similar to this should give you a record for every minute (or part thereof) between the open and close times on your base table - you may need to tweak it if you have specific requirements about start and end minutes being included.