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:

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: 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.