如何计算两个日期之间的差异,格式为YYYY-MM-DD hh: mm: ss,并以秒或毫秒为单位得到结果?


当前回答

select TO_CHAR(TRUNC(SYSDATE)+(to_date( '31-MAY-2012 12:25', 'DD-MON-YYYY HH24:MI') 
                             - to_date( '31-MAY-2012 10:37', 'DD-MON-YYYY HH24:MI')), 
        'HH24:MI:SS') from dual

——结果:01:48:00

好吧,这不是OP要求的,但这是我想做的:-)

其他回答

select 
unix_timestamp('2007-12-30 00:00:00') - 
unix_timestamp('2007-11-30 00:00:00');
SELECT TIMESTAMPDIFF(HOUR,NOW(),'2013-05-15 10:23:23')
   calculates difference in hour.(for days--> you have to define day replacing hour
SELECT DATEDIFF('2012-2-2','2012-2-1')

SELECT TO_DAYS ('2012-2-2')-TO_DAYS('2012-2-1')
SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
-- result: 22:00:59, the difference in HH:MM:SS format


SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); 
-- result: 79259  the difference in seconds

因此,您可以使用TIMESTAMPDIFF来实现您的目的。

为什么不干脆

从表中选择Sum(Date1 - Date2)

Date1和date2是datetime

select TO_CHAR(TRUNC(SYSDATE)+(to_date( '31-MAY-2012 12:25', 'DD-MON-YYYY HH24:MI') 
                             - to_date( '31-MAY-2012 10:37', 'DD-MON-YYYY HH24:MI')), 
        'HH24:MI:SS') from dual

——结果:01:48:00

好吧,这不是OP要求的,但这是我想做的:-)