Informix Timestamps
Pascal Brenner asked whether Informix row timestamps updated on insert or update could be used in an application. Vic Glass said Informix keeps timestamps per data page, not per row. Thomas J. Girsch said they do not map to wall-clock time. Art S. Kagel said there is no auto-maintained TIMESTAMP type; use a DATETIME YEAR TO FRACTION column with a DEFAULT, an update trigger and CURRENT, noting that USEOSTIME affects resolution.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Is anybody here know if we can use the timestamps of Informix in an application ? By timestamps, I mean the one which are updated when a row in inserted or updated. And if yes, how can we ?? Thanks Pascal pbrenner@technologist.com
I believe that Informix maintains a time stamp (actually two) in each data page, but does not have a row level time stamp. Many applications seem to include a user defined row level column for the time stamp, which is updated explicitedly during insert or update. Pascal Brenner wrote: > Is anybody here know if we can use the timestamps of Informix in an > application ? > By timestamps, I mean the one which are updated when a row in inserted or > updated. > And if yes, how can we ?? > > Thanks > > Pascal > > pbrenner@technologist.com
The Informix timestamp isn't meaningful in any useful sense. It uses an Informix-internal clocking mechanism that has no correlation whatsoever to a wall clock or a desk calendar. Pascal Brenner <pbrenner@ecg.com.au> wrote in message news:Dx023.2$Su4.15@news.syd.ip.net.au... > Is anybody here know if we can use the timestamps of Informix in an > application ? > By timestamps, I mean the one which are updated when a row in inserted or > updated. > And if yes, how can we ?? > > Thanks > > Pascal > > pbrenner@technologist.com > > >
Pascal Brenner wrote: > > Is anybody here know if we can use the timestamps of Informix in an > application ? > By timestamps, I mean the one which are updated when a row in inserted or > updated. > And if yes, how can we ?? Informix does not have a TIMESTAMP data type, ala Sybase & SQL Server, that is automatically maintained. You CAN use a DATETIME YEAR TO FRACTION type column and use a DEFAULT constraint for INSERTS and an TRIGGER FOR UPDATE on the other columns to maintain the timestamp during updates both using the CURRENT YEAR TO FRACTION syntax. However, note that the resolution of the Informix CURRENT function is only accurate to the nearest second if the ONCONFIG parameter USEOSTIME is set to zero and the actual resolution if USEOSTIME is set to one (1) is OS dependent, though most UNIXes support FRACTION(3) accurately. Turning USEOSTIME on is recommended against in the Informix manuals as it uses more system calls and adds to the general system burden. If you have 7.30+ you can substitute a TRIGGER FOR INSERT for the DEFAULT constraint and use a random number or an increasing number, stored in a table, that wraps back to zero after growing to 3, 4, or 5 digits as a pseudo-fractional part of the timestamp. Art S. Kagel