Solved

Query help needed

Posted on 2004-03-25
2
190 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 75 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
What You Need to Know when Searching for a Webhost Provider
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

734 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