Difference between two timestamp in seconds

Question: How can I get difference between two timestamp variables in seconds in Oracle?


Answer: Use interval datatype to store the difference. Example code is below.

DECLARE
  l_start_time TIMESTAMP;
  l_end_time   TIMESTAMP;
  l_sec        INTERVAL DAY(9) TO SECOND(6);
BEGIN
  l_start_time := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    NULL;
  END LOOP;
  l_end_time := systimestamp;


  l_sec := l_end_time - l_start_time;
  dbms_output.put_line('Seconds past=' || abs(extract(SECOND FROM l_sec) + extract(minute FROM l_sec) * 60 + extract(hour FROM l_sec) * 60 * 60 + extract(DAY FROM l_sec) * 24 * 60 * 60));
END;


Output is:
Seconds past=.001144

No comments :

Post a Comment