Querying application-period temporal data

Querying an application-period temporal table can return results for a specified time period.

About this task

When querying an application-period temporal table, you can include FOR BUSINESS_TIME in the FROM clause. Using FOR BUSINESS_TIME specifications, you can query the current, past, and future 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.

FROM value1 TO value2: Includes all the rows where the begin value for the period is greater than or equal to value1 and the end value for the period is less than value2. This means that the begin time is included in the period, but the end time is not.

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 an application-period temporal table, use the SELECT statement. For example, each of the following queries requests policy information for policy_id A123 from the result table in the Deleting data from an application-period temporal table topic. Each query uses a variation of the time period specification.

The policy_info table is as follows:

Table 1. Application-period temporal table: policy_info
policy_id	coverage	bus_start	bus_end
A123	12000	2008-01-01	2008-06-01
A123	14000	2008-06-01	2008-06-15
A123	16000	2008-08-15	2009-01-01
B345	18000	2008-03-01	2009-01-01
C567	25000	2008-01-01	2009-01-01

Query with no time period specification.

For example:

SELECT policy_id, coverage, bus_start, bus_end 
   FROM policy_info
   where policy_id = 'A123'

This query returns all three rows for policy A123.

A123, 12000, 2008-01-01, 2008-06-01
A123, 14000, 2008-06-01, 2008-06-15
A123, 16000, 2008-08-15, 2009-01-01

Query with FOR BUSINESS_TIME AS OF specified.
For example:
```
SELECT policy_id, coverage, bus_start, bus_end 
   FROM policy_info
   FOR BUSINESS_TIME AS OF '2008-07-15'
   where policy_id = 'A123'
```
This query does not return any rows. There are no rows for A123 where the begin value for the period is less than or equal to 2008-07-15 and the end value for the period is greater than 2008-07-15. Policy A123 had no coverage on 2008-07-15.
Query with FOR BUSINESS_TIME FROM...TO specified.
For example:
```
SELECT policy_id, coverage, bus_start, bus_end 
   FROM policy_info
   FOR BUSINESS_TIME FROM 
      '2008-01-01' TO '2008-06-15'
   where policy_id = 'A123'
```
This query returns two rows. The begin-column of a period is inclusive, while the end-column is exclusive. The row with a bus_end value of 2008-06-15 is valid until 06-14-2008 at midnight and so is less than value2.
```
A123, 12000, 2008-01-01, 2008-06-01
A123, 14000, 2008-06-01, 2008-06-15
```
Query with FOR BUSINESS_TIME BETWEEN...AND specified.
For example:
```
SELECT policy_id, coverage, bus_start, bus_end 
   FROM policy_info
   FOR BUSINESS_TIME BETWEEN
      '0001-01-01' AND '2008-01-01'
```
This query returns two rows. The rows with a bus_start value of 2008-01-01 are equal to value1 and are returned because the begin time of a period is included. Note that if a row had a bus_end column value of 2008-01-01, that row would be returned because its end time is equal to value1 and the end time of a period is included.
```
A123, 12000, 2008-01-01, 2008-06-01
C567, 25000, 2008-01-01, 2009-01-01
```

More examples

This section contains more querying application-period temporal table examples.

Querying a view

A view can be queried as if it were an application-period temporal table. Time period specifications (FOR BUSINESS_TIME) can be specified after the view reference.

CREATE VIEW policy_year_end(policy, amount, start_date, end_date)
   AS SELECT * FROM policy_info;

SELECT * FROM policy_year_end FOR BUSINESS_TIME AS OF '2008-12-31';

The SELECT on the view policy_year_end queries the policy_info table and returns all policies that were in effect at the end of 2008.

A123, 16000, 2008-08-15, 2009-01-01
B345, 18000, 2008-03-01, 2009-01-01
C567, 25000, 2008-01-01, 2009-01-01

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 BUSINESS_TIME AS OF '2008-02-28';

SELECT * FROM all_policies FOR BUSINESS_TIME BETWEEN
   FOR BUSINESS_TIME AS OF '2008-10-01';