Get Elapsed_Time

The query below gives me the start_Time and the End_Time

Select  Min (to_char (A. Run_Date, 'HH24:MI:SS')) Start_Time,
Max (to_char (B. Run_Date, 'HH24:MI:SS')) END_Time
From MyTable_1 A, MyTable_2 B;

START_TI END_TIME
-------- --------
19:00:01 19:39:11

I would like to get "Elapsed_Time" as well, which is End_Time - Start_Time. as a third column in the same query.

The output should be like:

Start_Time       End_Time      Elapsed_Time
---------------       -------------      --------------------
19:00:01          19:39:11         00:09:10
Kamal AgnihotriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>From MyTable_1 A, MyTable_2 B;

You realize this creates a Cartesian product?


Anyway:  There are many examples out there on extracting hours, minutes and seconds between two dates.

If the fields are timestamp instead of date, it is even easier.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorCommented:
Couldn't you just do the math:  colB - colA as difference?
0
slightwv (䄆 Netminder) Commented:
>>Couldn't you just do the math:  colB - colA as difference?

Yes, if they wanted the 'days' between them...

To get the hours, minutes and seconds, there is some additional math necessary.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
In this case it's a little trickier because they aren't really comparing the difference between two full dates,  but only the difference between the time portion of two dates.  That actually makes part of it easier since we know the difference will always be less than 24 hours


SELECT start_time, end_time, TO_CHAR(TRUNC(SYSDATE) + (b.seconds - a.seconds), 'hh24:mi:ss')
  FROM (SELECT MIN(TO_CHAR(run_date, 'HH24:MI:SS')) start_time,
               TO_NUMBER(MIN(TO_CHAR(run_date, 'sssss'))) seconds
          FROM mytable1) a,
       (SELECT MAX(TO_CHAR(run_date, 'HH24:MI:SS')) end_time,
               TO_NUMBER(MAX(TO_CHAR(run_date, 'sssss'))) seconds
          FROM mytable2) b;


I am making the assumption that end_time >= start_time

The query above will work regardless of whether the data types of either table are date or timestamp,  with or without time zones
0
sdstuberCommented:
SELECT start_time, end_time, TO_CHAR(TRUNC(SYSDATE) + (b.seconds - a.seconds), 'hh24:mi:ss')
  FROM (SELECT MIN(TO_CHAR(run_date, 'HH24:MI:SS')) start_time,
               TO_NUMBER(MIN(TO_CHAR(run_date, 'sssss'))) seconds
          FROM mytable1) a,
       (SELECT MAX(TO_CHAR(run_date, 'HH24:MI:SS')) end_time,
               TO_NUMBER(MAX(TO_CHAR(run_date, 'sssss'))) seconds
          FROM mytable2) b;
0
Kamal AgnihotriAuthor Commented:
Hi Sdstuber,

I am getting this.

START_TI    END_TIME        ELAPSED_
--------           --------------           -----------
19:00:01      19:39:11               00:00:00


 I think we are very close.
0
Mark GeerlingsDatabase AdministratorCommented:
I wrote a PL\SQL function many years ago (and posted it on this site before) to calculate the time between two date values.  That is attached.  Simply remove these four lines after the first "else" line, if you don't ever need the number of days to be included:
    days := trunc(end_tm - start_tm);
    if days > 0 then
      ret_val := to_char(days)||' days, ';
    end if;
TIMEBETW.SQL
0
Mark GeerlingsDatabase AdministratorCommented:
If you create that function, you can call it like this:

Select  Min (to_char (A. Run_Date, 'HH24:MI:SS')) Start_Time,
 Max (to_char (B. Run_Date, 'HH24:MI:SS')) END_Time,
 time_between (min(A. Run_Date), max(B. Run_Date), 'N') "Elapsed_Time"
 From MyTable_1 A, MyTable_2 B
where ...
0
slightwv (䄆 Netminder) Commented:
sdstuber must be tied up since he didn't respond.  It is a pretty simple change to fix his code.

Change:
TO_CHAR(TRUNC(SYSDATE) + (b.seconds - a.seconds), 'hh24:mi:ss')

To:
TO_CHAR(TRUNC(SYSDATE) + ((b.seconds - a.seconds)/60/60/24), 'hh24:mi:ss')
0
Kamal AgnihotriAuthor Commented:
Hi Markgeer,

The function idea I explored but had to abandon because the tables are not in my schema. Let me put it this way, I can ONLY query but can not create any procedures, functions etc. Hence the only thing that can be done is  a sql statement. I know the experts can do it.

Thanks a lot.
0
Kamal AgnihotriAuthor Commented:
slightwv, your suggestion fixed the issue. The question can now be closed.

Thanks every one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.