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