oracle取时间的小时_如何在oracle中减去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.

How can I do that?

解决方案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

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.