Time-based update detection

Some applications need to know database updates for certain time ranges, which might be used for replication of data, auditing scenarios, and so on. The ROW CHANGE TIMESTAMP expression provides this information. 

    ROW CHANGE TIMESTAMP FOR table-designator
returns a timestamp representing the time when a row was last changed, expressed in local time similar to CURRENT TIMESTAMP. For a row that was updated, this reflects the most recent update to the row. Otherwise, the value corresponds to the original insert of the row.
The value of the ROW CHANGE TIMESTAMP is different from the CURRENT TIMESTAMP in that it is guaranteed unique when assigned by the database per row per database partition. It is a local timestamp approximation of the modification time of each individual row inserted or updated. Since the value is always growing from earlier to later, it can become out of sync with the system clock if:
  • The system clock is changed
  • The row change timestamp column is GENERATED BY DEFAULT (intended for data propagation only) and a row is provided with an out of sync value.
The prerequisite for using the ROW CHANGE TIMESTAMP expression is that the table must have a row change timestamp column defined using the default precision for the timestamp data type, TIMESTAMP(6) (or TIMESTAMP - the default precision is 6). Every row returns the timestamp of when it was inserted or last updated. There are two methods in which the row change timestamp column can be part of the table:

  • The table was created using the FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP clause of the CREATE TABLE command. A ROW CHANGE TIMESTAMP expression returns the value of the column. For this category, the timestamp is precise. The row change timestamp in general when generated by the database is limited by speed of inserts and possible clock manipulations including DST adjustment.

  • The table was not created with a row change timestamp column, but one was later added using the FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP clause of the an ALTER TABLE statement. A ROW CHANGE TIMESTAMP expression returns the value of the column. For this category, the old (pre-alter) rows do not contain the actual timestamp until they are first updated or an offline table reorganization is performed.
    Note: The timestamp is an approximate time that the actual update occurred in the database, as of the system clock at the time and taking into account the limitation that no timestamps can be repeated within a database/table partition. In practice this is normally a very accurate representation of the time of the update. The row change timestamp, in general, when generated by the database, is limited by speed of inserts and possible clock manipulations including DST adjustments.

    Rows that were not updated since the ALTER TABLE statement will return the type default value for the column, which is midnight Jan 01, year 1. Only rows that were updated have a unique timestamp. Rows which have the timestamp materialized via an offline table reorganization return a unique timestamp generated during the reorganization of the table. A REORG TABLE operation with the INPLACE parameter is not sufficient as it does not materialize schema changes.

In either case, the timestamp of a row might also be updated if a redistribution is performed. If the row is moved from one database partition to another during a redistribution, then a new timestamp must be generated which is guaranteed to be unique at the target.