Re: date math

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1998/04/01
Message-ID: <6ftui8$boa1@hendrix.csufresno.edu>#1/1

Here's a snippet of code I use to calculate elapsed time in hours, minutes and seconds. It is run inside a package. Note the addition of 0.000001. This takes care of some floating point arithmetic rounding/truncation problems.

  FUNCTION ELAPSED_TIME(START_TIME IN DATE,END_TIME IN DATE)     RETURN VARCHAR2 IS
      ET NUMBER; -- Elapsed time
  BEGIN
    ET := END_TIME - START_TIME + 0.000001;

    RETURN('Elapsed time (HH:MM:SS) = '
          ||Ltrim(To_char(    Trunc(ET*24),        '99900'))||':'
          ||Ltrim(To_char(Mod(Trunc(ET*1440),60),  '00')) ||':'
          ||Ltrim(To_char(Mod(Trunc(ET*86400),60) ,'00')));

  END ELAPSED_TIME; Steve Cosner

In article <6frru3$go9$1_at_nnrp1.dejanews.com>,  <jeff.w.mcclure_at_ameritech.com> wrote:
>Folks,
>
>I have a couple of questions concerning the manipulation of date fields within
>sql. The applications tables contain several date fields (per record). These
>fields describe the beginning time and ending time for a given network event.
>Management would like to see some reports showing the amount of time
>(duration) of an individual event, as well as the average time an event takes.
>As it was put to me:
>
>duration = end_time - begin_time (on a per record basis)
>avg = (end_time - begin_time)/#_of_events
>
>How do I accomplish this in sql (PL/SQL)?
>
>I am running 8.0.3 on Solaris 2.5
>
>Any help would be greatly appreciated...
>
>Jeff McClure
>Specialist DB Analyst
>Ameritech New Media
>e-mail:jeff.w.mcclure_at_ameritech.com
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Apr 01 1998 - 00:00:00 CST