Re: Substracting two timestamp values

Now THAT is SERVICE!!

Thanks alot Paul, That's exactly what I needed!

Now I'll have a good weekend, I hope you do TOO!

Thaks Again,
Bob
bweber_at_onramp.net

Paul Passarelli <paulp_at_nospam.talkthru.com> wrote in article <01bca9ba$f8c9b0b0$be6964c7_at_kosh>...
>
> --
> Fri 08/15/1997
>
> Funny, I just had to do that yesterday. Not from SYS.AUD$ but from
> another table, but it's essentially the same.
>
>
> /*
>
> UserLogonViews.sql -- Copyright(c) 1997 Paul Passarelli
>
> 08/14/97 pmp .000 some views on the userlogon table
> ---------------------------------------------------------------------
>
> CREATE TABLE userlogon
> (
> userlogonid number(8) constraint pk_userlogonid
> primary key,
> userid number(8) constraint
> fk_userid_userlogon references userid DISABLE,
> logondate date ,
> logoffdate date ,
> logonapplication varchar2(20) ,
> logoffreason varchar2(10)
> )
> TABLESPACE USER_DATA
>
> */
>
>
> -- This one is completed and or aborted sessions
> create or replace view vw_userlogon
> (
> "User Name",
> "Application used",
> "Logoff Reason",
> "Logon Date-Time",
> "Duration of use"
> )
> as
> select
> useridname,
> logonapplication,
> logoffreason,
> to_char( logondate, '"Logon at" DD-MON-YY HH:MI AM') "logondate",
> to_char( to_date( to_char( 86400 * ( logoffdate - logondate ),'99999' ),
> 'SSSSS' ), '"Logoff after" HH24:MI:SS' ) "Duration"
> from userid, userlogon
> where userid.userid = userlogon.userid
> and 1 > (logoffdate - logondate)
> with read only
> ;
>
>
> -- This one is active logons i.e. less than 1 day old
> create or replace view vw_activeusers
> (
> "User Name",
> "Application used",
> "Logon Date-Time",
> "Duration of use"
> )
> as
> select
> useridname,
> logonapplication,
> to_char( logondate, '"Logon at" DD-MON-YY HH:MI AM') "logondate",
> to_char( to_date( to_char( 86400 * ( NOW - logondate ),'99999' ), 'SSSSS'
> ), '"Logged on for" HH24:MI:SS' ) "Duration"
> from userid, userlogon, (select SYSDATE as NOW from DUAL)
> where userid.userid = userlogon.userid
> and 1 > (NOW - logondate)
> and logoffdate is NULL
> with read only
> ;
>
>
> -- This one is stale logons i.e. more than 1 day old
> create or replace view vw_inactiveusers
> (
> "User Name",
> "Application used",
> "Logon Date-Time"
> )
> as
> select
> useridname,
> logonapplication,
> to_char( logondate, '"Logon at" DD-MON-YY HH:MI AM') "logondate"
> from userid, userlogon, (select SYSDATE as NOW from DUAL)
> where userid.userid = userlogon.userid
> and 1 < (NOW - logondate)
> and logoffdate is NULL
> with read only
> ;
> /
>
>
>
> One thing to keep in mind if the durations you're looking at are over
 24hr
> the format string 'SSSSS' is in the range of 0 to 86399. so you'd have to
> do the math.
>
>
> --Paul
> ---------|^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^|---------
> Legitimate replies must remove spam from my address.
> ---------|____________________________________________________|---------
> Space. The final frontier. These are the voyages of the starship
> Enterprise. Its five year mission to explore strange new worlds...
>
>
> Bob Weber <bweber_at_onramp.net> wrote in article
> <01bca985$3dd14280$f964020a_at_baweber>...
> > Hi,
> >
> > Can anyone help me with getting a resonable value when subtracting two
> > timestamps??
> > I'm using the AUD$ table and I wan to get the amount of time the
 session
> > lasted so I'm taking the logoff tiem and subtracting it from the time
> > stampstamp, both are datetime values. Unfortunately I can't seem to
 get
> > seconds and minutes out of the result.
> >
> > For Example:
> > SELECT
> > to_char(SYS.AUD$.LOGOFF$TIME,'hh:mm:ss'),
> > to_char(SYS.AUD$.TIMESTAMP,'hh:mm:ss'),
> > (SYS.AUD$.LOGOFF$TIME - SYS.AUD$.TIMESTAMP) SessionTime
> > FROM SYS.AUD$
> > WHERE SYS.AUD$.TIMESTAMP>'15-AUG-97'
> >
> > I would appreciate any thoughts!!
> > Thanks,
> > Bob
> >
> >
>
Received on Sat Aug 16 1997 - 00:00:00 CDT