Re: reading timestamp from audit trail files (SOLUTION)

Kleanthis Thanks for posting this. It is very useful to receive a summarised report of the outcomes of various options. rgds -- -- Peter Tashkoff NZ Kiwifruit Project Consultant/DBA Marketing Board. Standard Disclaimers apply This posting may not be used by any commercial organisation to vilify another. >>> Kleanthis Goozis <amerop@netrus.net> 24/July/1996 08:12am >>> Kleanthis Goozis wrote: > > Informix's Techinfo document #782 describes the format of > the audit trail header as follows: > [SNIP] > 3-6 integer A 4 byte > representation > of the date > and time > (number of > seconds > since Janu- > ary 1 , 1970.) > [SNIP] > > 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)? > > SOLUTION: select datetime(1970-01-01 00:00:00) year to second + a_time units second from .... NOTE: The datetime value returned is Greenwich Time, therefore to convert to for example U.S.Eastern Time you have to subtract 4*60*60 from a_time (4 hrs difference). i.e. select datetime(1970-01-01 00:00:00) year to second + (a_time - 4*60*60) units second from .... I would to thank everybody who responded to my posting. Nils Myklebust proposed solution looks correct but not allowed by the Informix SQL syntax parser (that's where I was having problems to begin with; I couldn't come up with the correct syntax). However, I borrowed the "a_time units second" part from him. John Prideaux's Stored Procedure works fine, but the above solution is much sorter and more elegant. Kleanthis