1. Junior Member
Join Date
Feb 2001
Posts
99
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:

substr(to_char(ir.released_date,'yyyy-mm-dd:hh24mi'),1,15) "OUT",
to_number(to_char(ir.released_date,'yyyymmddhh24mi') -
where ia.jms_number = ir.jms_number
and released_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

Join Date
Oct 2000
Location
Germany
Posts
1,185
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.

3. Code:
```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```

4. Junior Member
Join Date
Feb 2001
Posts
99
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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•