Example: Time criteria in the query
These sample queries request policy information from a system-period temporal table. Each query uses a variation of the FOR SYSTEM_TIME specification.
The POLICY_INFO temporal table and its associated history table that is used in the examples contains these rows:
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 |
-
Query with no time period specified
SELECT policy_id, coverage, sys_start, sys_end FROM policy_info WHERE policy_id = 'C567'
This query returns one row. The SELECT queries only the POLICY_INFO table. The history table is not queried because FOR SYSTEM_TIME was not specified.
Table 3. Result of query with no time period specified POLICY_ID COVERAGE SYS_START SYS_END C567 25000 2016-02-28-09.10.12.649592000000 9999-12-30-00.00.00.000000000000 -
Query with FOR SYSTEM_TIME AS OF clause specified
SELECT policy_id, coverage, sys_start, sys_end FROM policy_info FOR SYSTEM_TIME AS OF '2016-02-28-09.10.12.649592000000'
This query returns three rows. The SELECT queries both the POLICY_INFO and the HIST_POLICY_INFO tables. The row-begin column of the period is inclusive, while the row-end column is exclusive. The history table row with a SYS_END column value of 2016-02-28-09.10.12.649592000000 equals the AS OF value, but must be less than that value to be returned
Table 4. Result of query with a FOR SYSTEM_TIME AS OF period specified POLICY_ID COVERAGE SYS_START SYS_END A123 12000 2015-01-31-22.31.334959250000 9999-12-30-00.00.00.000000000000 C567 25000 2016-02-28-09.10.12.649592000000 9999-12-30-00.00.00.000000000000 B345 18000 2015-01-31-22.31.33.495925000000 2016-09-01-12.18.22.959254000000 -
Query with FOR SYSTEM_TIME FROM...TO specified.
SELECT policy_id, coverage, sys_start, sys_end FROM policy_info FOR SYSTEM_TIME FROM '0001-01-01-00.00.00.000000000000' TO '9999-12-30-00.00.00.000000000000' WHERE policy_id = 'C567'
This query returns two rows. The SELECT queries both the POLICY_INFO and the HIST_POLICY_INFO tables.
Table 5. Result of query with FOR SYSTEM_TIME FROM...TO specified. POLICY_ID COVERAGE SYS_START SYS_END C567 25000 2016-02-28-09.10.12.649592000000 9999-12-30-00.00.00.000000000000 C567 20000 2015-01-31-22.31.33.495925000000 2016-02-28-09.10.12.649592000000 -
Query with FOR SYSTEM_TIME BETWEEN...AND specified.
SELECT policy_id, coverage FROM policy_info FOR SYSTEM_TIME BETWEEN '2016-02-28-09.10.12.649592000000' AND '9999-12-30-00.00.00.000000000000'
This query returns three rows. The SELECT queries both the POLICY_INFO and the HIST_POLICY_INFO tables. The rows with a SYS_START column value of 2016-02-28-09.10.12.649592000000 are equal to value1 and are returned because the begin time of a period is included. The rows with a SYS_END column value of 2016-02-28-09.10.12.649592000000 are equal to value1 and are not returned because the end time of a period is not included.
Table 6. Result of query with FOR SYSTEM_TIME BETWEEN...AND specified. POLICY_ID COVERAGE A123 12000 C567 25000 B345 18000