Re: Internal Time Conversion

>From: jdiaz@accustaff.com (Jeff Diaz) >Date: 5 Aug 1997 16:12:05 GMT >X-Informix-List-Id: <news.41237> > >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? Another poor lost soul who didn't manage to make it to IWUC 97? Or at least, did not attend the session on I4GL Tips, etc, where we covered this in one of the slides... The value in the sysmaster:'informix'.syssessions.connected column is a Unix time value; the number of seconds since 'the Epoch', which was 1970-01-01 00:00:00. To convert the value to a regular datetime, use: SELECT DATETIME(1970-01-01 00:00:00) YEAR TO SECOND + connected UNITS SECOND FROM sysmaster:'informix'.syssessions; Note that this gives a UTC (GMT) time value; you would have to manually compensate for the timezone. So, for example, I'm in the Pacific timezone (8 hours west during winter, 7 hours west of UTC during the summer), and I get the output: SELECT sid,username,hostname,connected, DATETIME(1970-01-01 00:00:00) YEAR TO SECOND + connected UNITS SECOND AS ConnTime_UTC FROM sysmaster:syssessions; sid|username|hostname|connected|conntime_utc INTEGER|CHAR(8)|CHAR(16)|INTEGER|DATETIME YEAR TO SECOND 625|johnl|anubis|870807006|1997-08-05 18:50:06 I have another little program which converts Unix times to readable (local) times, and it gives: 870807006 = Tue Aug 05 11:50:06 1997 This is 7 hours different from the UTC value in the query, as it should be. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path.