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.
About this task
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.
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';Parent topic: Retrieving data by using the SELECT statement