DB2 10.5 for Linux, UNIX, and Windows

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