Thursday, September 9, 2010

Getting Milliseconds from Oracle Timestamps

http://www.dba-oracle.com/t_timestamp_math_elapsed_times.htm

An example procedure based on the link calculations.

create table test (a int, b int, primary key (a,b));

declare
startt timestamp;
endt timestamp;
ms int;
begin
dbms_output.enable(1000000);
startt:=systimestamp;
for i in 1 .. 1001
loop
insert into test values (i,i+1);
commit;
end loop;
endt:=systimestamp;
select sum(
(extract(hour from endt)-extract(hour from startt))*3600+
(extract(minute from endt)-extract(minute from startt))*60+
extract(second from endt)-extract(second from startt))*1000 into ms from dual;
dbms_output.put_line(ms);
end;
/

drop table test purge;

No comments:

Post a Comment