在两个 Oracle 时间戳之间获取秒数
汤姆·凯特 建议 使用 EXTRACT 获得差异:
Tom Kyte suggests to use EXTRACT to get the difference:
extract( day from (x-y) )*24*60*60+
extract( hour from (x-y) )*60*60+
...
这似乎比这更难阅读，也更慢，例如:
This seems to be harder to read and slower than this, for example:
( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400
<小时>
那么，在几秒钟内获得两个时间戳之间的差异的方法是什么?谢谢！
So, what is the way to get the difference between two Timestamps in seconds? Thanks!
推荐答案
最佳实践"
无论你做什么，将它包装在一个函数中，例如seconds_between (from_date, to_date) - 不管它是怎么做的(选择最有效的方法) - 然后你的代码在做什么就很明显了.
Whatever you do, wrap it in a function, e.g. seconds_between (from_date, to_date) - doesn't matter how it does it (choose the most efficient method) - then it will be perfectly obvious what your code is doing.
性能
我在笔记本电脑 (WinXP) 上使用以下测试用例在 11gR1 上测试了这两种方法.似乎 CAST 选项是最快的.(t1 是基线，t2 使用 extract 方法，t3 使用 cast 方法)
I tested the two methods on 11gR1 on my laptop (WinXP) with the test case below. It seems the CAST option is the fastest. (t1 is baseline, t2 used the extract method, t3 used the cast method)
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;
相关文章