Example: Time criteria in the view

These sample queries request policy information from a view built over a system-period temporal table.

The POLICY_INFO temporal table and its associated history table that is used in the examples are:

Table 1. System-period temporal table, POLICY_INFO
POLICY_ID COVERAGE SYS_START SYS_END TS_ID
A123 12000 2015-01-31-22.31.33.495925000000 9999-12-30-00.00.00.000000000000 2015-01-31-22.31.33.495925000000
C567 25000 2016-02-28-09.10.12.649592000000 9999-12-30-00.00.00.000000000000 2016-02-28-09.10.12.649592000000
Table 2. History table, HIST_POLICY_INFO
POLICY_ID COVERAGE SYS_START SYS_END TS_ID
C567 20000 2015-01-31-22.31.33.495925000000 2016-02-28-09.10.12.649592000000 2015-01-31-22.31.33.495925000000
B345 18000 2015-01-31-22.31.33.495925000000 2016-09-01-12.18.22.959254000000 2015-01-31-22.31.33.495925000000

Example 1: Create a view COVERAGE that references the system-period temporal table POLICY_INFO. Then, query the view and specify a FOR SYSTEM_TIME period specification to return rows that were current as of one year ago. Assume a current timestamp of 2016-05-17-14.45.31.434235000000.

CREATE VIEW coverage AS 
  SELECT policy_id, coverage 
    FROM policy_info;

SELECT * FROM coverage 
  FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 1 YEAR;

The SELECT queries both the POLICY_INFO and the HIST_POLICY_INFO tables and returns:

Table 3. Result of query of a view that has a period specification.
POLICY_ID COVERAGE
A123 12000
C567 20000
B345 18000
Example 2: Create a view COVERAGE that reference the system-period temporal table POLICY_INFO and return rows that were current as of one year ago. 
CREATE VIEW coverage AS 
  SELECT policy_id, coverage 
    FROM policy_info
    FOR SYSTEM_TIME AS CURRENT TIMESTAMP - 1 YEAR;

SELECT * FROM coverage;

The query of this view always returns rows that were current AS OF one year ago. Assuming a current timestamp of 2016-05-17-14.45.31.434235000000, the query returns the same results as the results that were returned in example one.