-
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
-
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.
David Knight
OCP DBA 8i, 9i, 10g
-
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
Jeff Hunter
-
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
-
That's right. .5 days is .5*24 or 12 hours or .5*(24*60) or 720 minutes.
Jeff Hunter
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|