Start of change

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:

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
  • 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
End of change