Querying system-period temporal data
Querying a system-period temporal table can return results for a specified point or period in time. Those results can include current values and previous historic values.
About this task
- AS OF value1
- Includes all the rows where the begin value for the period is less than or equal to value1 and the end value for the period is greater than value1. This enables you to query your data as of a certain point in time.
- FROM value1 TO value2
- Includes all the rows where the value of the begin column for the specified period in the row is less than value2, and the value of the end column for the specified period in the row is greater than value1. No rows are returned if value1 is greater than or equal to value2.
- BETWEEN value1 AND value2
- Includes all the rows where any time period overlaps any point in time between value1 and value2. A row is returned if the begin value for the period is less than or equal to value2 and the end value for the period is greater than value1.
Procedure
To query a system-period temporal table, use the SELECT statement. For example, each of the following queries requests policy information from the result tables in the Deleting data from a system-period temporal table topic. Each query uses a variation of the FOR SYSTEM_TIME specification.
The
policy_info
table
and its associated history table are as follows:
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
A123 | 12000 | 2010-01-31- 22.31.33. 495925000000 |
9999-12-30- 00.00.00. 000000000000 |
2010-01-31- 22.31.33. 495925000000 |
C567 | 25000 | 2011-02-28- 09.10.12. 649592000000 |
9999-12-30- 00.00.00. 000000000000 |
2011-02-28- 09.10.12. 649592000000 |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
C567 | 20000 | 2010-01-31- 22.31.33. 495925000000 |
2011-02-28- 09.10.12. 649592000000 |
2010-01-31- 22.31.33. 495925000000 |
B345 | 18000 | 2010-01-31- 22.31.33. 495925000000 |
2011-09-01- 12.18.22. 959254000000 |
2010-01-31- 22.31.33. 495925000000 |
More examples
This section contains more querying system-period temporal table examples.- Query using other valid date or timestamp values
- The
policy_info
table was created with its time-related columns declared asTIMESTAMP(12)
, so queries using any other valid date or timestamp value are converted to use TIMESTAMP(12) before execution. For example:
is converted and executed as:SELECT policy_id, coverage FROM policy_info FOR SYSTEM_TIME AS OF '2011-02-28'
SELECT policy_id, coverage FROM policy_info FOR SYSTEM_TIME AS OF '2011-02-28-00.00.00.000000000000'
- Querying a view
- A view can be queried as if it were a system-period temporal table.
FOR SYSTEM_TIME specifications can be specified after the view reference.
The SELECT on the viewCREATE VIEW policy_2011(policy, start_date) AS SELECT policy_id, sys_start FROM policy_info; SELECT * FROM policy_2011 FOR SYSTEM_TIME BETWEEN '2011-01-01-00.00.00.000000' AND '2011-12-31-23.59.59.999999999999';
policy_2011
queries both thepolicy_info
and thehist_policy_info
tables. Returned are all policies that were active at anytime in 2011 and includes the date the policies were started.
If a view definition contains a period specification, then queries against the view cannot contain period specifications. The following statements return an error due to multiple period specifications:A123, 2010-01-31-22.31.33.495925000000 C567, 2011-02-28-09.10.12.649592000000 C567, 2010-01-31-22.31.33.495925000000 B345, 2010-01-31-22.31.33.495925000000
CREATE VIEW all_policies AS SELECT * FROM policy_info; FOR SYSTEM_TIME AS OF '2011-02-28-09.10.12.649592000000'; SELECT * FROM all_policies FOR SYSTEM_TIME BETWEEN '2011-01-01-00.00.00.000000' AND '2011-12-31-23.59.59.999999999999';