SQL 从间隔数据类型中提取总秒数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10092032/
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
Extracting the total number of seconds from an interval data-type
提问by Ben
When subtracting timestampsthe return value is an intervaldata-type. Is there an elegant way to convert this value into the total number of (milli/micro) seconds in the interval, i.e. an integer.
减去时timestamps返回值是interval数据类型。是否有一种优雅的方法可以将此值转换为间隔中的（毫/微）秒总数，即整数。
The following would work, but it's not very pretty:
以下方法可行，但不是很漂亮：
select abs( extract( second from interval_difference )
+ extract( minute from interval_difference ) * 60
+ extract( hour from interval_difference ) * 60 * 60
+ extract( day from interval_difference ) * 60 * 60 * 24
)
from ( select systimestamp - (systimestamp - 1) as interval_difference
from dual )
Is there a more elegant method in SQL or PL/SQL?
SQL 或 PL/SQL 中是否有更优雅的方法？
采纳答案by zep
I hope this help:
我希望这会有所帮助：
zep@dev> select interval_difference
2 ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
3 from (select systimestamp - (systimestamp - 1) as interval_difference
4 from dual)
5 ;
INTERVAL_DIFFERENCE FRACT_SEC_DIFFERENCE
------------------------------------------------------------------------------- --------------------
+000000001 00:00:00.375000 86400,375
With your test:
通过您的测试：
zep@dev> select interval_difference
2 ,abs(extract(second from interval_difference) +
3 extract(minute from interval_difference) * 60 +
4 extract(hour from interval_difference) * 60 * 60 +
5 extract(day from interval_difference) * 60 * 60 * 24) as your_sec_difference
6 ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
7 ,round(sysdate + (interval_difference * 86400) - sysdate) as sec_difference
8 ,round((sysdate + (interval_difference * 86400) - sysdate) * 1000) as millisec_difference
9 from (select systimestamp - (systimestamp - 1) as interval_difference
10 from dual)
11 /
INTERVAL_DIFFERENCE YOUR_SEC_DIFFERENCE FRACT_SEC_DIFFERENCE SEC_DIFFERENCE MILLISEC_DIFFERENCE
------------------------------------------------------------------------------- ------------------- -------------------- -------------- -------------------
+000000001 00:00:00.515000 86400,515 86400,515 86401 86400515
zep@dev>
回答by Zhaoping Lu
An easy way:
一个简单的方法：
select extract(day from (ts1-ts2)*86400) from dual;
The idea is to convert the interval value into days by times 86400 (= 24*60*60). Then extract the 'day' value which is actually second value we wanted.
这个想法是将间隔值转换为天数 86400 (= 24*60*60)。然后提取“天”值，这实际上是我们想要的第二个值。
回答by Waldo
I've found this to work. Apparently, if you do arithmetics with timestamps they are converted to some internal datatype that, when substracted from each other, returns the interval as a number.
我发现这有效。显然，如果您使用时间戳进行算术运算，它们将转换为某种内部数据类型，当相互减去时，将间隔作为数字返回。
Easy? Yes. Elegant? No. Gets the work done? Oh yeah.
简单？是的。优雅的？不。完成工作了吗？哦耶。
SELECT ( (A + 0) - (B + 0) ) * 24 * 60 * 60
FROM
(
SELECT SYSTIMESTAMP A,
SYSTIMESTAMP - INTERVAL '1' MINUTE B
FROM DUAL
);
回答by Korhan Ozturk
Unfortunately, I don't think that there is an alternative (or more elegant) way of calculating total seconds from an interval type in pl/sql. As this articlementions:
不幸的是，我认为没有其他（或更优雅的）方法可以从 pl/sql 中的间隔类型计算总秒数。正如这篇文章提到的：
... unlike .NET, Oracle provides no simple equivalent to TimeSpan.TotalSeconds.
therefore extracting day, hour etc from the interval and multiplying them with corresponding values seems like the only way.
因此从间隔中提取天、小时等并将它们与相应的值相乘似乎是唯一的方法。
回答by Tomislav Matas
Based on zep's answer, I wrapped things up into a function for your convenience:
根据zep 的回答，为了您的方便，我将内容打包成一个函数：
CREATE OR REPLACE FUNCTION intervalToSeconds(
pMinuend TIMESTAMP , pSubtrahend TIMESTAMP ) RETURN NUMBER IS
vDifference INTERVAL DAY TO SECOND ;
vSeconds NUMBER ;
BEGIN
vDifference := pMinuend - pSubtrahend ;
SELECT EXTRACT( DAY FROM vDifference ) * 86400
+ EXTRACT( HOUR FROM vDifference ) * 3600
+ EXTRACT( MINUTE FROM vDifference ) * 60
+ EXTRACT( SECOND FROM vDifference )
INTO
vSeconds
FROM DUAL ;
RETURN vSeconds ;
END intervalToSeconds ;
回答by moshik
Use following query:
使用以下查询：
select (cast(timestamp1 as date)-cast(timestamp2 as date))*24*60*60)
回答by Louis Saglio
Similar to @Zhaoping Lu answer but directly extracting seconds instead of getting them from the number of days.
类似于@Zhaoping Lu 的回答，但直接提取秒而不是从天数中获取。
SELECT extract(second from (end_date - start_date)) as "Seconds number"
FROM my_table
(worked on PostgresSQL 9.6.1)
（在 PostgresSQL 9.6.1 上工作）
回答by Arun Kumar
SELECT to_char(ENDTIME,'yyyymmddhh24missff')-to_char(STARTTIME,'yyyymmddhh24missff') AS DUR FROM DUAL; yyyymmddhh24miss- WILL GIVE DURATION IN SEC yyyymmddhh24mi DURATION IN MIN yyyymmddhh24 - DURATION - HOURS yyyymmdd DURATION IN DAYS
SELECT to_char(ENDTIME,'yyyymmddhh24missff')-to_char(STARTTIME,'yyyymmddhh24missff') AS DUR FROM DUAL; yyyymmddhh24miss- 将给出 SEC 中的 DURATION yyyymmddhh24mi DURATION IN MIN yyyymmddhh24 - DURATION - HOURS yyyymmdd DURATION IN DAYS
