asked on

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David VanZandt

Couldn't you just do the math: colB - colA as difference?

slightwv (䄆 Netminder)

>>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.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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;

Kamal Agnihotri

ASKER

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.

Mark Geerlings

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

Mark Geerlings

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 ...

slightwv (䄆 Netminder)

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')

Kamal Agnihotri

ASKER

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.

Kamal Agnihotri

ASKER

slightwv, your suggestion fixed the issue. The question can now be closed.

Thanks every one.