Link to home
Start Free TrialLog in
Avatar of tim_cs
tim_csFlag for United States of America

asked on

Query help needed

I was wondering if there is any way to pull two times from an oracle database and get the total time between the two? I have the following query and just about everything is near working as far as I know except for the time difference. You can see in my query what I have randomly tried.

select
    a.display, A.hire_dt, b.Used, c.schedule, D.discr_time
from
    problogs_team A,
    ( select
            team, count(cal_id) AS used
            from
                    problogs_cal
            where
                    cal_type = 'V' AND
                    START_DT > '1-jan-2004' AND
                    START_DT < '25-mar-2004'
            GROUP BY team
    ) B,
            ( select
            team, count(cal_id) as schedule
            from
                    problogs_cal
            where
                    cal_type = 'V' and
                    start_dt > '25-mar-2004' and
                    start_dt < '1-jan-2005'
            GROUP BY TEAM
    ) C,
    ( select
            team, SUM(TO_DATE(START_DT, 'HH24:MI:SS ') + TO_DATE(END_DT, 'HH24:MI:SS ')) AS discr_time
            from
                    problogs_cal
            where
                    cal_type = 'D' AND
                    START_DT > '1-jan-2004' AND
                    START_DT < '25-mar-2004'
          GROUP BY team
            ) D
 where
   
    A.TEAM = B.TEAM (+) AND
    A.TEAM = C.TEAM (+) AND
    A.TEAM = D.TEAM (+) AND
    A.MGR_ID = 'ZZZZZZZ;' AND
    A.PASS IS NOT NULL
ASKER CERTIFIED SOLUTION
Avatar of ATAHAC
ATAHAC

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of morphman
morphman

1. To convert to seconds:

select (date2 - date1)*24*60*60 from table;

24 hours x 60 minutes x 60 secs

2. To convert to hours:

select (date2 - date1)*24 from table;

HTH