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
DB2 10.5 for Linux, UNIX, and Windows