获取两个 Oracle 时间戳之间的秒数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3957909/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting seconds between two Oracle Timestamps
提问by Peter Lang
Tom Kyte suggeststo use EXTRACTto 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!
那么，在几秒钟内获得两个时间戳之间的差异的方法是什么？谢谢！
回答by Jeffrey Kemp
"Best Practice"
“最佳实践”
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.
不管你做什么，把它包装在一个函数中，例如seconds_between (from_date, to_date)- 它是如何做的并不重要（选择最有效的方法） - 那么你的代码在做什么就很明显了。
Performance
表现
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 extractmethod, t3 used the castmethod)
我使用下面的测试用例在我的笔记本电脑 (WinXP) 上的 11gR1 上测试了这两种方法。似乎 CAST 选项是最快的。（t1 为基线，t2 使用该extract方法，t3 使用该cast方法）
t1 (nothing) 3
t2 (extract) 338
t3 (cast) 101
t1 (nothing) 3
t2 (extract) 336
t3 (cast) 100
Test script
测试脚本
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;
回答by user3820019
Alternative:
I found this to work as well to get the difference in seconds including milliseconds.
It's even save for time-zones with "daylight saving" while the extract method would have a problem.
Unfortunately the difference between t1 and t2 is limited for the result to be right. Casting timestamps to date format is not an option because the fractions of seconds are lost.
替代方案：
我发现这也可以用于获得包括毫秒在内的秒数差异。
它甚至可以保存具有“夏令时”的时区，而提取方法会出现问题。不幸的是，t1 和 t2 之间的差异是有限的，结果是正确的。将时间戳转换为日期格式不是一种选择，因为会丢失几分之一秒。
select (sysdate + (t2 - t1)*1000 - sysdate) * 86.4 from
(select
to_timestamp('2014-03-30 01:00:10.111','YYYY-MM-DD HH24:MI:SS.FF') at time zone 'MET' t1,
to_timestamp('2014-03-30 03:00:10.112','YYYY-MM-DD HH24:MI:SS.FF') at time zone 'MET' t2
from dual);
回答by Adrian Smith
I have always used the second way i.e. compare the DATEs (which gives you the number of days difference, with a fractional part), and the multiply by the factor you want to give you number of hours, minutes, seconds, or whatever.
我一直使用第二种方法，即比较日期（它为您提供天数差异，带有小数部分），然后乘以您想要为您提供小时数、分钟数、秒数或其他任何值的因子。
I think it's good, and easy to read.
我觉得挺好的，读起来很轻松。
回答by Nick Pierpoint
Personally, I find:
就个人而言，我觉得：
extract(day from (x-y))*24*60*60 + ... + extract(second from (x-y))
clearer in purpose than...
目的比……更明确
( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400
to get the difference in seconds.
以秒为单位获得差异。
Tom's method takes a few more keystrokes but the intent is clear.
Tom 的方法需要多敲几次键，但意图很明确。
回答by user3801158
for fast and easy use:
快速简便的使用：
extract( day from(t2 - t1)*24*60*60)
Example:
例子：
with dates as (
select
to_timestamp('2019-06-18 22:50:00', 'yyyy-mm-dd hh24:mi:ss') t1
, to_timestamp('2019-06-19 00:00:38', 'yyyy-mm-dd hh24:mi:ss') t2
from dual
)
select
extract( day from(t2 - t1)*24*60*60) diff_in_seconds
from dates
;
Output:
输出：
DIFF_IN_SECONDS
---------------
638
回答by imaya
to_number(to_char(t2, 'yyyymmddhh24missff')) - to_number(to_char(t1, 'yyyymmddhh24missff'))
