Example: Time criteria specified by using the CURRENT TEMPORAL SYSTEM_TIME special register
These sample queries request policy information from a system-period temporal table for a specific point in time by using the CURRENT TEMPORAL SYSTEM_TIME special register.
The POLICY_INFO temporal table and its associated history table that is used in the examples are:
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 |
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: Set the CURRENT TEMPORAL SYSTEM_TIME special register to one year before the current timestamp. Assume a current timestamp of 2016-05-17-14.45.31.434235000000.
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP – 1 YEAR;
The following query of the system-period temporal table returns results as of one year ago.
SELECT policy_id, coverage FROM policy_info;
Since POLICY_INFO is a system-period temporal table and the CURRENT TEMPORAL SYSTEM_TIME special register is not null, the query is run as:
SELECT policy_id, coverage FROM policy_info FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME;
The SELECT queries both the POLICY_INFO and the HIST_POLICY_INFO tables and returns:
Table 3. Result of query with the special register set. POLICY_ID COVERAGE A123 12000 C567 20000 B345 18000 -
Example 2: Set the CURRENT TEMPORAL SYSTEM_TIME special register to a timestamp and reference a system-period temporal table in view definitions.
CREATE VIEW coverage AS SELECT policy_id, coverage FROM policy_info; SET CURRENT TEMPORAL SYSTEM_TIME = TIMESTAMP('2016-02-28-09.10.12.649592000000'); SELECT * FROM coverage;
Since the view references POLICY_INFO, which is a system-period temporal table, and the CURRENT TEMPORAL SYSTEM_TIME is set, the table reference in the view has FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME implicitly added to it. The SELECT queries both the POLICY_INFO and the HIST_POLICY_INFO tables and returns:
Table 4. Result of query with the special register set. POLICY_ID COVERAGE A123 12000 C567 25000 B345 18000 - Example 3: Set the CURRENT TEMPORAL SYSTEM_TIME special
register and reference a system-period temporal table in a subselect.
CREATE VIEW customer_policy AS SELECT * FROM customer_info WHERE cust_policy_id IN (SELECT policy_id FROM policy_info); SET CURRENT TEMPORAL SYSTEM_TIME = TIMESTAMP('2016-02-28-09.10.12.649592000000'); SELECT * FROM customer_policy;
The query in this example involves a view over a non system-period temporal table that references a system-period temporal table.
The CURRENT TEMPORAL SYSTEM TIME special register is applied to all system-period temporal tables in a view. It is ignored for any other table reference. For this query of a view, FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM TIME is implicitly added to the POLICY_INFO table reference.
- Example 4: Set the special register and run a query that
contains a time period specification. This is an invalid query.
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 1 YEAR; SELECT * FROM policy_info FOR SYSTEM_TIME AS OF TIMESTAMP('2016-02-28-09.10.12.649592000000');
An error is returned because there are multiple time period specifications. The special register was set to a non-null value and the query also specified a time. When the query uses a FOR SYSTEM_TIME clause, the CURRENT TEMPORAL SYSTEM_TIME special register must be the NULL value.