onstat -ntt time format

Hi,what time routine can I use for to get something like onstat -ntt output? The following query Select a.sid session, b.sh_curtime now, b.sh_boottime boot, a.connected connect, (b.sh_curtime - a.connected) duration from sysmaster:sysscblst a, sysmaster:sysshmvals b where a.sid > 0 tell me information about users's connection time, but I can't to transform this integers to hh:mm:sec format. I tried with l2date, but return today. I saw the l2date code too, but it is not what I need. Somebody has some idea? Thanks in advance Paola ---------------------------------------------------------------- Ce.S.P.I. - UNLP

Hi, i had this problem a long time ago and l2date was my first idea. Then i created my own SPL to have l2datetime ... ... have a lot of fun ! gerd here it is: create procedure l2datetime(xx int) returning datetime year to second as zeitpunkt;define dd int; define hh int; define mm int; define ss int; define dt datetime year to second; define ff int; let dd = 0; let hh = 0; let mm = 0; let ss = 0; let ff = 0; let dt = CURRENT year to second; let dd = trunc(xx/86400); --tage 60*60*24 let ff = xx - dd*86400; let hh = trunc(ff/3600); --stunden let ff = ff - hh*3600; let mm = trunc(ff/60); --minuten let ff = ff - mm*60; let ss = ff; --sekunden select ("1970-01-01 00:00:00"::datetime year to second ) + dd units day + hh units minute + mm units minute + ss units second into dt from systables where tabid = 1; return dt; end procedure; pamadeo@cespi.unlp.edu.ar schrieb am 22.03.05 22:26:55: > > > Hi,what time routine can I use for to get something like onstat -ntt output? > > The following query > Select a.sid session, > b.sh_curtime now, > b.sh_boottime boot, > a.connected connect, > (b.sh_curtime - a.connected) duration > from sysmaster:sysscblst a, > sysmaster:sysshmvals b > where a.sid > 0 > > tell me information about users's connection time, but I can't to transform this > integers to hh:mm:sec format. > > I tried with l2date, but return today. I saw the l2date code too, but it is not > what I need. > > Somebody has some idea? > > Thanks in advance > Paola > > > > ---------------------------------------------------------------- > Ce.S.P.I. - UNLP > ______________________________________________________________ Verschicken Sie romantische, coole und witzige Bilder per SMS! Jetzt bei WEB.DE FreeMail: http://f.web.de/?mc=021193

Try this: select a.sid session, dbinfo( 'utc_to_datetime', b.sh_curtime ) now, dbinfo( 'utc_to_datetime', b.sh_boottime ) boot, dbinfo( 'utc_to_datetime', a.connected ) connect,b.sh_curtime - a.connected duration from sysmaster:sysscblst a, sysmaster:sysshmvals b where a.sid > 0 Patrick -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of gerd kaluzinski Sent: Tuesday, March 22, 2005 3:12 PM To: ids@iiug.org Subject: Re: onstat -ntt time format [4591] Hi, i had this problem a long time ago and l2date was my first idea. Then i created my own SPL to have l2datetime ... .. have a lot of fun ! gerd here it is: create procedure l2datetime(xx int) returning datetime year to second as zeitpunkt;define dd int; define hh int; define mm int; define ss int; define dt datetime year to second; define ff int; let dd = 0; let hh = 0; let mm = 0; let ss = 0; let ff = 0; let dt = CURRENT year to second; let dd = trunc(xx/86400); --tage 60*60*24 let ff = xx - dd*86400; let hh = trunc(ff/3600); --stunden let ff = ff - hh*3600; let mm = trunc(ff/60); --minuten let ff = ff - mm*60; let ss = ff; --sekunden select ("1970-01-01 00:00:00"::datetime year to second ) + dd units day + hh units minute + mm units minute + ss units second into dt from systables where tabid = 1; return dt; end procedure; pamadeo@cespi.unlp.edu.ar schrieb am 22.03.05 22:26:55: > > > Hi,what time routine can I use for to get something like onstat -ntt output? > > The following query > Select a.sid session, > b.sh_curtime now, > b.sh_boottime boot, > a.connected connect, > (b.sh_curtime - a.connected) duration > from sysmaster:sysscblst a, > sysmaster:sysshmvals b > where a.sid > 0 > > tell me information about users's connection time, but I can't to transform this > integers to hh:mm:sec format. > > I tried with l2date, but return today. I saw the l2date code too, but it is not > what I need. > > Somebody has some idea? > > Thanks in advance > Paola > > > > ---------------------------------------------------------------- > Ce.S.P.I. - UNLP > ______________________________________________________________ Verschicken Sie romantische, coole und witzige Bilder per SMS! Jetzt bei WEB.DE FreeMail: http://f.web.de/?mc=021193