如何在oracle中减去2个日期以小时和分钟为单位获取结果

问题描述：

我想减去2个日期，并以小数点后的小时和分钟表示结果.

I want to subtract 2 dates and represent the result in hour and minute in one decimal figure.

我有下表，并且是以这种方式进行的，但结果并不理想.

I have the following table and I am doing it in this way but the result is not as desired.

有一些细微的变化，我敢肯定这是简单的算术，但我做得不好.

There is some slight variation, I'm sure this is simple arithmetic but I'm not getting it right.

select start_time, end_time, (end_time-start_time)*24 from 
come_leav;



START_TIME          END_TIME            (END_TIME-START_TIME)*24    
------------------- ------------------- ------------------------      
21-06-2011 14:00:00 21-06-2011 16:55:00  2.9166667      
21-06-2011 07:00:00 21-06-2011 16:50:00  9.8333333      
21-06-2011 07:20:00 21-06-2011 16:30:00  9.1666667

我想要以下结果(结束时间-开始时间).

I want the result (end_time-start_time) as below.


16:55- 14:00 = 2.55      
16:50-07:00 = 9.5      
16:30-7:20 = 9.1 and so on.

我该怎么办?

答

SQL> edit
Wrote file afiedt.buf

  1  select start_date
  2      , end_date
  3      , (24 * extract(day from (end_date - start_date) day(9) to second))
  4          + extract(hour from (end_date - start_date) day(9) to second)
  5          + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR.MINUTE"
  6* from t
SQL> /

START_DATE          END_DATE            HOUR.MINUTE
------------------- ------------------- -----------
21-06-2011 14:00:00 21-06-2011 16:55:00        2.55
21-06-2011 07:00:00 21-06-2011 16:50:00         9.5
21-06-2011 07:20:00 21-06-2011 16:30:00         9.1

对于那些遇到此代码的人来说，应注意的是小数部分是实际的分钟差异，而不是一小时的一部分.因此，.5代表50 minutes，而不是30 minutes.

It should be noted for those coming across this code that the decimal portions are ACTUAL minute differences, and not part of an hour. .5, therefore, represents 50 minutes, not 30 minutes.

如何在oracle中减去2个日期以小时和分钟为单位获取结果

相关推荐