[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query help needed

Posted on 2004-03-25
2
Medium Priority
?
202 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

649 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