Blog

Oracle PL/SQL: Time Difference

The following script show how to calculate the time difference between 2 execution points:

declare
    thediff number;
    TIME1 timestamp;
    TIME2 timestamp;
    i number;
begin

    TIME1 := SYSDATE;   

    for i in 1..1000000000 loop
      null;
    end loop;

    TIME2 := SYSDATE;
   
    DBMS_OUTPUT.PUT_LINE('STARTED: ' || TIME1);
    DBMS_OUTPUT.PUT_LINE('ENDED: ' || TIME2);

    thediff := extract(day from (TIME2-TIME1))*24*60*60
             + extract(hour from (TIME2-TIME1))*60*60
             + extract(minute from (TIME2-TIME1))*60
             + extract(second from (TIME2-TIME1));
    DBMS_OUTPUT.PUT_LINE('execution time: ' || thediff || ' seconds');

end; 

STARTED: 21-NOV-13 16.09.59.000000
ENDED: 21-NOV-13 16.10.35.000000
execution time: 36 seconds

Leave a Reply