日期算术 - 日数小时或秒的日期之间的差异
在 oracle 中,可以使用减法找到两个 DATE
之间的差异(以天为单位和/或其中的分数):
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
输出两个日期之间的天数:
DIFFERENCE 89
和:
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
输出两个日期之间的天数:
DIFFERENCE 1.0425
通过将该数字分别乘以 24
,24*60
或 24*60*60
可以得到小时,分钟或秒的差异。
可以更改上一个示例,以使用以下内容获取两个日期之间的天,小时,分钟和秒:
SELECT TRUNC( difference ) AS days,
TRUNC( MOD( difference * 24, 24 ) ) AS hours,
TRUNC( MOD( difference * 24*60, 60 ) ) AS minutes,
TRUNC( MOD( difference * 24*60*60, 60 ) ) AS seconds
FROM (
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
);
(注意:使用 TRUNC()
而不是 FLOOR()
来正确处理负差异。)
输出:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
前面的例子也可以通过使用 NUMTODSINTERVAL()
将数值差转换为间隔来解决 :
SELECT EXTRACT( DAY FROM difference ) AS days,
EXTRACT( HOUR FROM difference ) AS hours,
EXTRACT( MINUTE FROM difference ) AS minutes,
EXTRACT( SECOND FROM difference ) AS seconds
FROM (
SELECT NUMTODSINTERVAL(
TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL
);