DB2 10.5 for Linux, UNIX, and Windows

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

When querying a system-period temporal table, you can include FOR SYSTEM_TIME in the FROM clause. Using FOR SYSTEM_TIME specifications, you can query the current and past state of your data. Time periods are specified as follows:

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.

See the following section for some sample queries.

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:

Table 1. System-period temporal table: policy_info
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

Table 2. History table: hist_policy_info
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

Query with no time period specification. For example:
```
SELECT policy_id, coverage
   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.
```
C567, 25000
```
Query with FOR SYSTEM_TIME AS OF specified. For example:
```
SELECT policy_id, coverage
   FROM policy_info
   FOR SYSTEM_TIME AS OF 
      '2011-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 begin column of a period is inclusive, while the end column is exclusive. The history table row with a sys_end column value of 2011-02-28-22.31.33.495925000000 equals value1, but it must be less than value1 in order to be returned.
```
A123, 12000
C567, 25000
B345, 18000
```

Query with FOR SYSTEM_TIME FROM..TO specified. For example:

SELECT policy_id, coverage, sys_start, sys_end
   FROM policy_info
   FOR SYSTEM_TIME FROM 
      '0001-01-01-00.00.00.000000' 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.

C567, 25000, 2011-02-28-09.10.12.649592000000, 9999-12-30-00.00.00.000000000000
C567, 20000, 2010-01-31-22.31.33.495925000000, 2011-02-28-09.10.12.649592000000

Query with FOR SYSTEM_TIME BETWEEN..AND specified. For example:
```
SELECT policy_id, coverage
   FROM policy_info
   FOR SYSTEM_TIME BETWEEN 
      '2011-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 2011-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 2011-02-28-09.10.12.649592000000 are equal to value1 and are not returned because the end time of a period is not included.
```
A123, 12000
C567, 25000
B345, 18000
```

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 as TIMESTAMP(12), so queries using any other valid date or timestamp value are converted to use TIMESTAMP(12) before execution. For example:

SELECT policy_id, coverage
   FROM policy_info
   FOR SYSTEM_TIME AS OF '2011-02-28'

is converted and executed as:

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.

CREATE 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';

The SELECT on the view policy_2011 queries both the policy_info and the hist_policy_info tables. Returned are all policies that were active at anytime in 2011 and includes the date the policies were started.

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

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:

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';