Querying bitemporal data

Querying a bitemporal table can return results for a specified time period. Those results can include current values, previous historic values, and future values.

About this task

When querying a bitemporal table, you can include FOR BUSINESS_TIME, FOR SYSTEM_TIME, or both in the FROM clause. Using these time period 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. This enables you to query your data as of a certain point in time.

FROM value1 TO value2: Includes all the rows where the begin value for the period is equal to or greater than 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 a bitemporal table, use the SELECT statement. For example, each of the following queries requests policy information for policy_id A123 from the result tables in the Deleting data from a bitemporal table topic. Each query uses a variation of the time period specification.

The policy_info table and its associated history table are as follows:

Table 1. Bitemporal table: policy_info
policy_id	coverage	bus_start	bus_end	sys_start	sys_end	ts_id
A123	12000	2008-01-01	2008-06-01	2011-02-28- 09.10.12. 64959200000	9999-12-30- 00.00.00. 000000000000	2011-02-28- 09.10.12. 64959200000
A123	14000	2008-06-01	2008-06-15	2011-09-01- 12.18.22. 959254000000	9999-12-30- 00.00.00. 000000000000	2011-09-01- 12.18.22. 959254000000
A123	16000	2008-08-15	2009-01-01	2011-09-01- 12.18.22. 959254000000	9999-12-30- 00.00.00. 000000000000	2011-09-01- 12.18.22. 959254000000
B345	18000	2008-03-01	2009-01-01	2011-02-28- 09.10.12. 64959200000	9999-12-30- 00.00.00. 000000000000	2011-02-28- 09.10.12. 64959200000
C567	25000	2008-01-01	2009-01-01	2011-02-28- 09.10.12. 64959200000	9999-12-30- 00.00.00. 000000000000	2011-02-28- 09.10.12. 64959200000

Table 2. History table: hist_policy_info
policy_id	coverage	bus_start	bus_end	sys_start	sys_end	ts_id
A123	12000	2008-01-01	2008-07-01	2010-01-31- 22.31.33. 495925000000	2011-02-28- 09.10.12. 64959200000	2010-01-31- 22.31.33. 495925000000
A123	16000	2008-07-01	2009-01-01	2010-01-31- 22.31.33. 495925000000	2011-02-28- 09.10.12. 64959200000	2010-01-31- 22.31.33. 495925000000
B345	18000	2008-01-01	2009-01-01	2010-01-31- 22.31.33. 495925000000	2011-02-28- 09.10.12. 64959200000	2010-01-31- 22.31.33. 495925000000
C567	20000	2008-01-01	2009-01-01	2010-01-31- 22.31.33. 495925000000	2011-02-28- 09.10.12. 64959200000	2010-01-31- 22.31.33. 495925000000
A123	14000	2008-06-01	2008-07-01	2011-02-28- 09.10.12. 64959200000	2011-09-01- 12.18.22. 959254000000	2011-09-01- 12.18.22. 959254000000
A123	14000	2008-07-01	2008-08-01	2011-02-28- 09.10.12 .64959200000	2011-09-01- 12.18.22. 959254000000	2011-09-01- 12.18.22. 959254000000
A123	16000	2008-08-01	2009-01-01	2011-02-28- 09.10.12 .64959200000	2011-09-01- 12.18.22. 959254000000	2011-09-01- 12.18.22. 959254000000

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 three rows. The SELECT statement queries only the policy_info table. The history table is not queried because FOR SYSTEM_TIME was not specified.
```
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 SYSTEM_TIME FROM...TO specified.

For example:

SELECT policy_id, coverage, bus_start, bus_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 = 'A123'

This query returns eight rows. The SELECT statement queries both the policy_info and the hist_policy_info tables.

A123, 12000, 2008-01-01, 2008-06-01
A123, 14000, 2008-06-01, 2008-06-15
A123, 16000, 2008-08-15, 2009-01-01
A123, 12000, 2008-01-01, 2008-07-01
A123, 16000, 2008-07-01, 2009-01-01
A123, 14000, 2008-06-01, 2008-07-01
A123, 14000, 2008-07-01, 2008-08-01
A123, 16000, 2008-08-01, 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. The SELECT statement queries only the policy_info table and 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. The history table is not queried because FOR SYSTEM_TIME was not specified.

Query with FOR BUSINESS_TIME AS OF and FOR SYSTEM_TIME FROM...TO specified.

For example:

SELECT policy_id, coverage, bus_start, bus_end 
   FROM policy_info
   FOR BUSINESS_TIME AS OF '2008-07-15'
   FOR SYSTEM_TIME FROM 
      '0001-01-01-00.00.00.000000' TO '9999-12-30-00.00.00.000000000000'
   where policy_id = 'A123'

This query returns two rows. The SELECT queries both the policy_info and the hist_policy_info tables. The returned rows are found in the history table.

A123, 16000, 2008-07-01, 2009-01-01
A123, 14000, 2008-07-01, 2008-08-01