CODEWITHSUNDEEP
sqloracle-databaseplsql
Asdfg
Asdfg

Reputation: 12203

Oracle - Insert one row per hour between two times

Given two times, how do i insert one row per hour in the Oracle table?

Start Time: 3.00 PM

End Time: 6.00 PM

Name: 'Asdfg'

Expected Data to be generated:

Name      StartTime       EndTime
ASDFG       3.00           4.00
ASDFG       4.00,          5.00
ASDFG       5.00           6.00

Upvotes: 3

Views: 858

Answers (2)

DCookie
DCookie

Reputation: 43523

This does it without PL/SQL, in one insert. You could parameterize the start and end times and use it in your procedure:

INSERT INTO tbl (NAME, starttime, endtime)
(SELECT 'ASDFG', t1, t2
   FROM (SELECT to_char((to_date('3.00 PM','HH.MI AM')+(LEVEL-1)/24),'HH.MI AM') t1
              , to_char((to_date('3.00 PM','HH.MI AM')+LEVEL/24), 'HH.MI AM') t2
           FROM dual 
        CONNECT BY LEVEL <= (to_date('6.00 PM','HH.MI AM') - to_date('3.00 PM','HH.MI AM')) * 24));

Upvotes: 2

vc 74
vc 74

Reputation: 38179

CurrentTime := StartTime;
WHILE CurrentTime <= EndTime
LOOP
     INSERT INTO MY_TABLE VALUES (CurrentTime);

     CurrentTime  := CurrentTime + (1 / 24);

END LOOP;

COMMIT;

Should do the trick (I did not try it...)

Upvotes: 0

Related Questions