Re: subtracting date / time in SQL

From: Spencer <spencerp_at_swbell.net>
Date: Mon, 11 Jun 2001 20:40:28 -0500
Message-ID: <xoeV6.106$qR1.12120@nnrp1.sbc.net>

"Syltrem" <syltrem_at_videotron.ca.spammenot> wrote in message news:Wq8V6.10622$Cl1.668657_at_wagner.videotron.net...
> I am trying to calculate the elapsed time for a connected session
> Example:
> select sysdate - logon_time from v$session where sid=35;
>
> SYSDATE-LOGON_TIME
> ------------------
> ,278344907
>
> Which is approximately 6 hours (a quarter of a day).
>
> Now I want to display this in time format, I tried the following
> select to_char(sysdate - logon_time, 'HH:MI:SS') from v$session where
> sid=35
> *
> ERROR at line 1:
> ORA-01481: invalid number format model
>
> and also this
> select to_char(to_date(sysdate - logon_time), 'HH:MI:SS') from v$session
> where sid=35;
>
> What am I doing wrong?
>
> Thanks for helping me!
> --
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS related web site)
> To reply to myself directly, remove .spammenot from my address
>
>

the format string elements HH, MI, SS, etc. are valid in the TO_CHAR function only when the first argument is of type DATE. you can either convert your expression back to a date or do the math yourself.

TO_CHAR(TRUNC(sysdate)+(sysdate-logon_time), 'HH24:MI:SS)

this will correctly only for durations >= 0 and < 1.

to support durations over 1 day, you can use an expression to find the whole number of days between the two dates...

or you can create expressions to do the math yourself:

to get whole hours:

  TRUNC((SYSDATE-LOGON_TIME)*24) to get whole minutes:

MOD( TRUNC((SYSDATE-LOGON_TIME)*24*60),60) to get whole seconds:

MOD( TRUNC((SYSDATE-LOGON_TIME)*24*60*60),60) to get the number of hours, minutes, and seconds into a string in format HHHH:MI:SS, you can use an expression something like this:

TO_CHAR( TRUNC((SYSDATE-LOGON_TIME)*24) ) ||':'||
 TO_CHAR( MOD( TRUNC((SYSDATE-LOGON_TIME)*24*60),60), 'FM00') ||':'||
TO_CHAR( MOD( TRUNC((SYSDATE-LOGON_TIME)*24*60*60),60), 'FM00') instead of including all of this in your sql, it may be of benefit to create a
package which includes the conversion functions:

CREATE OR REPLACE PACKAGE dur
IS

FUNCTION to_hhhhmiss
(an_days IN NUMBER
)RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(to_hhhhmiss,WNDS,RNPS,WNPS);

FUNCTION to_hhhhmiss
(ad_date1 IN DATE
,ad_date2 IN DATE DEFAULT SYSDATE
)RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(to_hhhhmiss,WNDS,RNPS,WNPS);

END dur;
/

CREATE OR REPLACE PACKAGE BODY dur
IS

FUNCTION to_hhhhmiss
(an_days IN NUMBER
)RETURN VARCHAR2
IS
BEGIN
  RETURN TO_CHAR(TRUNC( an_days *24))

     ||':'||
     TO_CHAR(MOD(TRUNC( an_days *1440),60),'FM00')
     ||':'||
     TO_CHAR(MOD(TRUNC( an_days *86400),60),'FM00');

END to_hhhhmiss;

FUNCTION to_hhhhmiss
(ad_date1 IN DATE
,ad_date2 IN DATE DEFAULT SYSDATE
)RETURN VARCHAR2
IS
BEGIN
  RETURN to_hhhhmiss(ad_date2-ad_date1); END to_hhhhmiss;

END dur;
/

• optionally make the package available to everyone: GRANT EXECUTE ON dur TO PUBLIC;

and then use the function in your SQL:

select dur.to_hhhhmiss( sysdate - logon_time ) from v$session where sid=35;

-or-

select dur.to_hhhhmiss( logon_time ) from v$session where sid=35;

btw... this has not been compiled or tested. i'll leave that for you to do.

HTH Received on Mon Jun 11 2001 - 20:40:28 CDT