Querying temporal tables

You can query a temporal table to retrieve data based on the time criteria that you specify.

About this task

A temporal table that includes a system period (SYSTEM_TIME) and is defined with system-period data versioning is a system-period temporal table. A temporal table that includes an application period (BUSINESS_PERIOD) is an application-period temporal table.

Procedure

To query a temporal table:

Issue a SELECT statement, and in the table-reference of the FROM clause, specify a period-specification. A period-specification consists of the following clauses:

FOR SYSTEM TIME or FOR BUSINESS TIME to indicate whether you want to query a system-period temporal table or an application-period temporal table
AS OF, FROM, or BETWEEN to indicate the time criteria for which you want data

Begin general-use programming interface information.

The following example shows how you can request data based on time criteria from a system-period temporal table.

SELECT policy_id, coverage FROM policy_info
FOR SYSTEM_TIME AS OF '2009-01-08-00.00.00.000000000000';

Likewise, the following example shows how you can request data based on time criteria from an application-period temporal table.

SELECT policy_id, coverage FROM policy_info
FOR BUSINESS_TIME AS OF '2008-06-01';

End general-use programming interface information.

If you are requesting historical data from a system-period temporal table that is defined with system-period data versioning, DB2® rewrites the query to include data from the history table.