Elapsed Time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Elapsed Time

  1. #1
    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:

    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

  2. #2
    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.


    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    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. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    That's right. .5 days is .5*24 or 12 hours or .5*(24*60) or 720 minutes.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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
  •  



Click Here to Expand Forum to Full Width