Re: reading timestamp from audit trail files

Kleanthis Goozis wrote: > > Informix's Techinfo document #782 describes the format of > the audit trail header as follows: > > Byte Data Type Values stored > ----------------------------------------------------------------------- > 1 -2 character aa - for added records > length 2 dd - for deleted records > rr - before update > ww - after update > > 3-6 integer A 4 byte > representation > of the date > and time > (number of > seconds > since Janu- > ary 1 , 1970.) > > 7-8 smallint process id > > 9-10 smallint userid > > 11 -14 integer isam record number > ----------------------------------------------------------------------- > > Any ideas of how to translate (using SQL) the timestamp (bytes 3-6) to a > human readable format (something like mm/dd/yy mm:ss)? > Try the following stored procedure (which can pretty easily be converted to a 4GL function): create procedure cvt_time(p_time integer) returning datetime year to second; define no_days, no_hours, no_minutes, no_seconds integer; define partial_days, partial_hours, partial_minutes integer; define adjusted_time integer; define dat1, dat2 date; define dt datetime year to second; -- Function to convert a Unix timestamp to a DATETIME YEAR TO SECOND -- initialize a date variable to contain the days from 12/31/1899 -- to 01/01/1970 let dat1 = "01/01/1970"; -- Adjust for GMT, we are 6 hours behind -- Keep in mind that this changes for daylight savings times -- Just subtract 6 hours from the reported value -- there are 60*60 seconds in an hour let adjusted_time = p_time - (6*60*60); -- Find the total number of days in seconds -- First find the number of seconds left over partial days let partial_days = mod(adjusted_time, 24*60*60); -- Find the number of days in the portion of the original number -- that are whole days let no_days = (adjusted_time - partial_days)/(24*60*60); -- Calculate the number of hours in the remainder from the days -- find out how many of the seconds are not part of a complete hour let partial_hours = mod(partial_days, 60*60); -- Find the number of whole hours let no_hours = (partial_days - partial_hours)/(60*60); -- Calculate the number of minutes in the remainder from the hours -- Find out how many of the seconds are not part of a complete minute -- The remainder will be the left over seconds let partial_minutes = mod(partial_hours, 60); -- Find the number of whole minutes let no_minutes = (partial_hours - partial_minutes)/60; -- Add the number of days since 01/01/1970 to the initialized date let dat2 = dat1 + no_days; -- Create a string variable to convert to a datetime variable let dt = extend(dat2, year to second); let dt = dt + no_hours units hour; let dt = dt + no_minutes units minute; let dt = dt + partial_minutes units second; return dt; end procedure; This is based on an Informix document I received from Tech Support back in 1993. -- ------------------------------------------------------------------------------- John Prideaux e-mail: alastl@ix.netcom.com Phone: (217)839-2121 Aladdin Steel, Inc. Fax: (217)839-4327 -------------------------------------------------------------------------------