Querying temporal tables

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

To query a temporal table, use one of the following methods:

• Specify the time criteria in the query: 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
  

  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';

  

  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.

• Specify the time criteria by using special registers:
  The advantage of this method is that you can change the time criteria later and not have to modify the SQL and then rebind the application.
  1. Write the SELECT statement without any time criteria specified.
  2. When you bind the application, ensure that the appropriate bind options are set as follows:
     • If you are querying a system-period temporal table, ensure that SYSTIMESENSITIVE is set to YES.
     • If you are querying an application-period temporal table, ensure that BUSTIMESENSITIVE is set to YES.
  3. Before you call the application, set the appropriate special registers to the timestamp value for which you want to query data:
     • If you are querying a system-period temporal table, set CURRENT TEMPORAL SYSTEM_TIME.
     • If you are querying an application-period temporal table, set CURRENT TEMPORAL BUSINESS_TIME.
  For example, assume that you have system-period temporal table STT with the column POLICY_ID and you want to retrieve data from one year ago. You can set the CURRENT TEMPORAL SYSTEM_TIME period as follows:

  SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP – 1 YEAR ;

  Then you can issue the SELECT statement:

  SELECT * FROM STT
  WHERE POLICY_ID = 123 ;

  Db2 interprets this SELECT statement as follows:

  SELECT * FROM STT
  FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME
  WHERE POLICY_ID = 123 ;