?
Solved

Query help needed

Posted on 2004-03-25
2
Medium Priority
?
198 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
0
Comment
Question by:tim_cs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
ATAHAC earned 300 total points
ID: 10689442
You can use to_char(START_DT,'SSSSS') which gives you seconds past midnight . Knowing that 24hours = 86400 seconds
you can use
to_date(mod(sum(to_char(START_DT,'SSSSS') + to_char(START_DT,'SSSSS')), 86400),'SSSSS')
it will give the sum of hours  but only past midnight you can calculate the sum of days by
FLOOR(sum(to_char(START_DT,'SSSSS') + to_char(START_DT,'SSSSS')) / 86400)
0
 
LVL 6

Expert Comment

by:morphman
ID: 10692485
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
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question