Re: Internal Time Conversion
Jeff Diaz wrote:
>
> I am trying to create an SQL script to tell how long users are logged
> in to our system. To do this, I was intending to use the connected
> field of the syssessions table. This field appears to store the
> internal datetime format for the time the user connected to the
> database. Is there a way to convert this integer into a format that
> resembles a time? Is this possible? I've done this before on other
> systems, but I can't figure our what I had done before, or if Informix
> can do it.
>
> Is this a possibility, or is there a better way?
>
> Thanks
>
> Jeff Diaz
> jdiaz@accustaff.com
Jeff,
One way you could do this is by writing a stored procedure to accept the
system time as an integer input and calculate the difference between the
input time and the connected time for each session. For example:
CREATE PROCEDURE session_times( sys_time INT )
RETURNING INT, CHAR(8), INT;
DEFINE p_sid INT;
DEFINE p_user CHAR(8);
DEFINE p_conntime INT;
FOREACH
SELECT sid, username, sys_time - connected
INTO p_sid, p_user, p_conntime
FROM syssessions
ORDER BY 3 DESC
RETURN p_sid, p_user, p_conntime WITH RESUME;
END FOREACH;
END PROCEDURE;
This solution will require you to get the integer representation of the
system time, but this is done easily enough in C with function time().
A simpler solution would be a stored proc that can convert CURRENT to an
integer representation instead of passing it as an input, however, I
could not figure a way to perform the conversion in SQL.
HTH,
Scott
********************************************************************
* Scott C. Campbell | Voice : (703)902-8218 *
* Sr. Software Development Specialist | Fax : (703)790-3391 *
* Landmark Systems Corporation | E-mail: scott@landmark.com *
* 8000 Towers Crescent Drive | *
* Vienna, VA 22182 | http://www.landmark.com *
********************************************************************