We help IT Professionals succeed at work.

Query help needed

tim_cs
tim_cs asked
on
Medium Priority
237 Views
Last Modified: 2013-12-24
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
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.