On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, June 25, 2008

Calculating Elapsed Time

A table (Time) is created with one date field (Curr_Min) in which, periodically, the Sysdate is entered.

Using the Lead function, the time from the next row is brought up next to the current row time so one can be subtracted from the other.

The calculation for elapsed time is a bit complicated since we're dealing with time rolling past midnight. (Thanks to Ray Dooley for this calculation.)
Column Elapsed_Time Heading Elapsed|HH:MI:SS
Column Row_Time Heading Row|Time
Column Next_Time Heading Next|Time

Select To_Char(Curr_Min, 'dd Dy Hh24:Mi:Ss')
As
Row_Time,
To_Char(Next_Min, 'dd Dy Hh24:Mi:Ss')
As
Next_Time,
--------------------------------------------------
To_Char(
Case
When Next_Secs >= Row_Secs
Then To_Date((Next_Secs-Row_Secs),'sssss' )
Else To_Date((86399 -Row_Secs)+ Next_Secs,'sssss')
End
,'Hh24:Mi:Ss'
)
As
Elapsed_Time
From
--+--------------------------------------------------------------------+
--| The subquery gets the current and next time formatted for display
--| and also in Secs in order to make the parent query easier to read.
--+--------------------------------------------------------------------+
(
Select Curr_Min,
-----------------------------------------------------------
Lead(Curr_Min,1,Null) Over (Order By Curr_Min)
As
Next_Min,
-----------------------------------------------------------
To_Char(Curr_Min,'sssss')
As
Row_Secs,
-----------------------------------------------------------
To_Char(Lead(Curr_Min,1,Null)
Over (Order By Curr_Min),'sssss')
As
Next_Secs
From Time
)
;
See Oracle documentation for further detail:
Lead

No comments:

Post a Comment