oracle两个时间戳之间的秒数,在两个Oracle时间戳之间取几秒钟

“最佳实践”

无论你做什么,把它包装在一个功能中,例如seconds_between(from_date,to_date) – 无论它如何操作(选择最有效的方法) – 那么你的代码将是非常明显的.

我在笔记本电脑(WinXP)上测试了11gR1上的两种方法,下面是测试用例.看来CAST选项是最快的. (t1是基线,t2使用提取方法,t3使用cast方法)

t1 (nothing) 3

t2 (extract) 338

t3 (cast) 101

t1 (nothing) 3

t2 (extract) 336

t3 (cast) 100

测试脚本

declare

x TIMESTAMP := SYSTIMESTAMP;

y TIMESTAMP := TRUNC(SYSDATE);

n PLS_INTEGER;

lc CONSTANT PLS_INTEGER := 1000000;

t1 PLS_INTEGER;

t2 PLS_INTEGER;

t3 PLS_INTEGER;

begin

t1 := DBMS_UTILITY.get_time;

for i in 1..lc loop

n := i;

end loop;

t1 := DBMS_UTILITY.get_time - t1;

t2 := DBMS_UTILITY.get_time;

for i in 1..lc loop

n := extract(day from (x-y))*24*60*60

+ extract(hour from (x-y))*60*60

+ extract(minute from (x-y))*60

+ extract(second from (x-y));

end loop;

t2 := DBMS_UTILITY.get_time - t2;

t3 := DBMS_UTILITY.get_time;

for i in 1..lc loop

n := ( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400;

end loop;

t3 := DBMS_UTILITY.get_time - t3;

dbms_output.put_line('t1 (nothing) ' || t1);

dbms_output.put_line('t2 (extract) ' || t2);

dbms_output.put_line('t3 (cast) ' || t3);

end;