Finding rows that were changed within a specified period of time
You can filter rows based on the time that they were updated. For example, you might want to find all rows in a particular table that have been changed in the last 7 days.
Procedure
To find the rows that were changed within a specified period of time:
Specify the ROW CHANGE TIMESTAMP expression in the predicate of your SQL statement.
Recommendation: Ensure that the table has a ROW CHANGE TIMESTAMP column that was defined prior to the time period that you want to query. This column ensures that Db2 returns only those rows that were updated in the given time period.
If the table does not have a ROW CHANGE TIMESTAMP column, Db2 returns all rows on each page that has had any changes within the given time period. In this case, your result set can contain rows that have not been updated in the give time period, if other rows on that page have been updated or inserted.
Examples
- Example
- Suppose that the TAB table has a ROW CHANGE TIMESTAMP column and that you want to return all of the records that have changed in the last 30 days. The following query returns all of those rows.
SELECT * FROM TAB WHERE ROW CHANGE TIMESTAMP FOR TAB <= CURRENT TIMESTAMP AND ROW CHANGE TIMESTAMP FOR TAB >= CURRENT TIMESTAMP - 30 days;
- Example
- Suppose that you want to return all of the records that have changed since 9:00 AM January 1, 2004. The following query returns all of those rows.
SELECT * FROM TAB WHERE ROW CHANGE TIMESTAMP FOR TAB >= '2004-01-01-09.00.00';