Elapsed Time

[DcsoBob]

Okay all u PL/SQL gurus.

I am trying to write a function that will return the elapsed time or difference between two date values. My goal is to find differences of 4 hours or greater.

For example. Somone is booked into a jail at 14/2314and leaves the jail at 15/0324. By normal caluclations, thats 370 minutes oracle gives me a value of 8010 when I do the subtraction using the following query:

select ia.jms_number,substr(to_char(ia.admitted_date,'yyyy-mm-dd:hh24mi'),1,15) "IN",
substr(to_char(ir.released_date,'yyyy-mm-dd:hh24mi'),1,15) "OUT",
to_number(to_char(ir.released_date,'yyyymmddhh24mi') -
to_char(ia.admitted_date,'yyyymmddhh24mi')) "DIFF"
from inmate_release ir, inmate_admission ia
where ia.jms_number = ir.jms_number
and released_date like '%OCT-02%'
and admitted_date like '%OCT-02%'

I am pretty sure I have solved this before, but cannot remember how it was done....

As a rule, is there any clean way of handling elapsed time or converting time.

Thanks

[dknight]

If minutes are the degree of granularity, use the fact that there are 24*60 == 1440 minutes per day.

So four hours is 4*60 == 240 minutes.

240/1440 = .16666... day.

Now subtract your two DATES. If the difference is greater than .16666 then you have found a record with difference greater than 4 hours.

[marist89]

SQL> select * from xyz;

START_DT            END_DT
------------------- -------------------
10/15/2002 11:10:03 10/15/2002 06:10:03

SQL> select start_dt - end_dt number_of_days from xyz;

NUMBER_OF_DAYS
--------------
    .208333333

SQL> select (start_dt - end_dt) * 24 number_of_hours from xyz;

NUMBER_OF_HOURS
---------------
              5

SQL>  select (start_dt - end_dt) * (24*60) number_of_minutes from xyz;

NUMBER_OF_MINUTES
-----------------
              300

[DcsoBob]

Thanks, got it to work. By what I see here, is this telling me that when I subtract two dates straight out, i get the number of days between the two dates by default?

In both cases, ya'll had me multiply the result by 24 to get the hours, or by (24*60) to get teh minutes?

Thanks again

[marist89]

That's right. .5 days is .5*24 or 12 hours or .5*(24*60) or 720 minutes.

[stecal]

Do you get more time credit (like two minutes credit for one served) when the officer is beating you in your cell?