Oracle 中的日期时间差异计算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19151115/
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
Date Time differences calculation in Oracle
提问by user2037445
I have a situation where I need to get the differences between two columns.
我有一种情况需要获取两列之间的差异。
Column 1: 01-OCT-13 10:27:15
Column 2: 01-OCT-13 10:28:00
第 1 栏：01-OCT-13 10:27:15
第 2 栏：01-OCT-13 10:28:00
I need to get the differences between those above two columns. I tried using '-' operator but the output is not in an expected way.
I need output as follows: 00-00-00 00:00:45
我需要了解上述两列之间的差异。我尝试使用“-”运算符，但输出不是预期的方式。
我需要输出如下：00-00-00 00:00:45
回答by Swapnil Patil
Try this query but it is not tested
试试这个查询，但它没有经过测试
SELECT extract(DAY
FROM diff) days,
extract(hour
FROM diff) hours,
extract(MINUTE
FROM diff) minutes,
extract(SECOND
FROM diff) seconds,
extract(YEAR
FROM diff) years
FROM
(SELECT (CAST (to_date(t.column1, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP) -CAST (to_date(t.column2, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP)) diff
FROM tablename AS t)
回答by Dba
Try this too,
也试试这个
WITH T(DATE1, DATE2) AS
(
SELECT TO_DATE('01-OCT-13 10:27:15', 'DD-MON-YY HH24:MI:SS'),TO_DATE('01-OCT-13 10:28:00', 'DD-MON-YY HH24:MI:SS') FROM DUAL
)
SELECT floor(date2 - date1)
|| ' DAYS '
|| MOD(FLOOR ((date2 - date1) * 24), 24)
|| ' HOURS '
|| MOD (FLOOR ((date2 - date1) * 24 * 60), 60)
|| ' MINUTES '
|| MOD (FLOOR ((date2 - date1) * 24 * 60 * 60), 60)
|| ' SECS ' time_difference
FROM T;
回答by Pooh
You can use the below query to find the difference seconds:
您可以使用以下查询来查找差异seconds：
select 24*60*60*
(to_date('01-OCT-13 10:28:00', 'yyyy-mm-dd hh24:mi:ss')-
to_date('01-OCT-13 10:27:15', 'yyyy-mm-dd hh24:mi:ss')) diff_secs
from dual;
