RE: Calculating # of days,hours,mins

From: Brian MacLean <bmaclean_at_homebid.com>
Date: Fri, 12 May 2000 13:49:46 -0700
Message-Id: <10495.105567@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFBC53.9B2FA884

Content-Type: text/plain;

charset="iso-8859-1"

SQL_at_dev1:HBDEV1:8.1.5:OPS$ORACLE:38,9160> create or replace function tm(v_date1 date, v_date2 date) return varchar2 is   2 v_rtn varchar2(20);
  3 begin
  4 v_rtn :=
  5 lpad(trunc(v_date1 - v_date2) ,2,'0') || ':' ||

  6  		        lpad(trunc((mod(v_date1 - v_date2,1) / (1/86400)) /
3600)	  ,2,'0') || ':' ||
  7  		    lpad(trunc(mod((mod(v_date1 - v_date2,1) / (1/86400)),
3600) / 60),2,'0') || ':' ||
  8  	    lpad(trunc(mod(mod((mod(v_date1 - v_date2,1) / (1/86400)),

3600), 60)),2,'0');
9 return v_rtn;
10 end;
11 /

Function created.

SQL_at_dev1:HBDEV1:8.1.5:OPS$ORACLE:38,9160> select tm(sysdate, sysdate - 1.5) "DD:HH:MI:SS" from dual;

DD:HH:MI:SS

01:12:00:00

SQL_at_dev1:HBDEV1:8.1.5:OPS$ORACLE:38,9160>

Brian P. Mac Lean
Senior Oracle Database Administrator
OCPv8/Oracle Master
HomeBid.Com
8700 N. Gainey Center Drive
Scottsdale, AZ 85258

Tel:480.609.4624
Cel:602.617.6075
Fax:480.609.4646
Net:brian.maclean_at_homebid.com

-----Original Message-----

From: Kevin Martin [mailto:kevin.martin_at_catapultsystems.com] Sent: Friday, May 12, 2000 10:39 AM
To: Multiple recipients of list ORACLE-L Subject: Calculating # of days,hours,mins

Hi List.

I have a SQL question that should be fairly easy, but I haven't quite gotten it nailed down yet.

I am storing the in_date for a particular record. I need to calculate the number of days, hours, and minutes that have elapsed since the record was entered.

It should look something like this:

RECORD	AGE


---------------	--------

Item1		2 Days, 9 Hours, 32 Minutes

If any of you has done something similar, would you mind sharing your solution or steering me in the right direction?

Thanks.
-km

--

Author: Kevin Martin
INET: kevin.martin_at_catapultsystems.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01BFBC53.9B2FA884

Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0">

<TITLE>RE: Calculating # of days,hours,mins</TITLE>
</HEAD>
<BODY>
<BR>

SQL_at_dev1:HBDEV1:8.1.5:OPS$ORACLE:38,9160> create = or replace function tm(v_date1 date, v_date2 date) return varchar2 = is
   2   v_rtn = varchar2(20);

<BR><FONT SIZE=3D1 FACE=3D"Terminal">&nbsp; 3&nbsp; begin</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Terminal">&nbsp; 4&nbsp;&nbsp;&nbsp; v_rtn =
:=3D</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Terminal">&nbsp; 5&nbsp; &nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

        =          lpad(trunc(v_date1 - =

v_date2)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp; ,2,'0') || ':' ||</FONT>

6     =         =         lpad(trunc((mod(v_date1 - = v_date2,1) / (1/86400)) / 3600)       =   ,2,'0') || ':' ||
   7     =             = lpad(trunc(mod((mod(v_date1 - v_date2,1) / (1/86400)), 3600) / = 60),2,'0') || ':' ||
   8     =     lpad(trunc(mod(mod((mod(v_date1 - v_date2,1) / = (1/86400)), 3600), 60)),2,'0');
   9   return = v_rtn;

<BR><FONT SIZE=3D1 FACE=3D"Terminal">&nbsp;10&nbsp; end;</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Terminal">&nbsp;11&nbsp; /</FONT>
</P>

Function created.

SQL_at_dev1:HBDEV1:8.1.5:OPS$ORACLE:38,9160> select = tm(sysdate, sysdate - 1.5) "DD:HH:MI:SS" from dual;

DD:HH:MI:SS <FONT SIZE=3D1 =

FACE=3D"Terminal">------------------------------------------------------=


--------------------------</FONT>

01:12:00:00

SQL_at_dev1:HBDEV1:8.1.5:OPS$ORACLE:38,9160>

</P>
<BR>
<BR>
<BR>
<BR>
<BR>

Brian P. Mac = Lean
 Senior = Oracle Database Administrator

<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">OCPv8/Oracle Master</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">HomeBid.Com</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">8700 N. =

Gainey Center Drive
 Scottsdale, = AZ  85258

<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Tel:480.609.4624</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Cel:602.617.6075</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Fax:480.609.4646</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Net:brian.maclean_at_homebid.com</FONT>
</P>
<BR>

-----Original Message----- From: Kevin Martin [<A = HREF=3D"mailto:kevin.martin_at_catapultsystems.com">mailto:kevin.martin_at_cat= apultsystems.com</A>]
 Sent: Friday, May 12, 2000 10:39 = AM
 To: Multiple recipients of list = ORACLE-L
 Subject: Calculating # of = days,hours,mins

Hi List.

I have a SQL question that should be = fairly easy, but I haven't quite gotten it nailed down yet.

I am storing the in_date for a = particular record.  I need to calculate the number of days, hours, and minutes = that have elapsed since the record was entered.

It should look something like = this:

<P><FONT SIZE=3D2 FACE=3D"Arial">RECORD&nbsp; AGE</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">--------------- --------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Item1&nbsp;&nbsp; =

2 Days, 9 Hours, 32 = Minutes

If any of you has done something = similar, would you mind sharing your solution or steering me in the right = direction?

<P><FONT SIZE=3D2 FACE=3D"Arial">Thanks.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">-km</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT>

Author: Kevin Martin   INET: = kevin.martin_at_catapultsystems.com

Fat City Network = Services    -- (858) 538-5051  FAX: (858) = 538-5051
 San Diego, = California        -- Public Internet = access / Mailing Lists
 <FONT SIZE=3D2 =

FACE=3D"Arial">---------------------------------------------------------=

-----------

 To REMOVE yourself from this mailing = list, send an E-Mail message
 to: ListGuru_at_fatcity.com (note EXACT = spelling of 'ListGuru') and in
 the message BODY, include a line = containing: UNSUB ORACLE-L
 (or the name of mailing list you want = Received on Fri May 12 2000 - 15:49:46 CDT