在两个Oracle时间戳之间获取秒数 [英] Getting seconds between two Oracle Timestamps
问题描述
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;
这篇关于在两个Oracle时间戳之间获取秒数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!