CURRENT TEMPORAL BUSINESS_TIME special register
The CURRENT TEMPORAL BUSINESS_TIME special register specifies a TIMESTAMP(12) value that is used in the default BUSINESS_TIME period specification for references to application-period temporal tables.
When
an application-period temporal table is referenced and the value in
effect for the CURRENT TEMPORAL BUSINESS_TIME special register is
represented by CTBT, which is the non-null
value, the following period specification is implicit:
FOR BUSINESS_TIME AS OF CTBT
When
an application-period temporal table is the target of an UPDATE or
DELETE statement and the value in effect for the CURRENT TEMPORAL
BUSINESS_TIME special register is not the null value, the following
additional predicate is implicit:
bt_begin <= CURRENT TEMPORAL BUSINESS_TIME
AND bt_end > CURRENT TEMPORAL BUSINESS_TIME
where bt_begin
and bt_end
are
the begin and end columns of the BUSINESS_TIME period of the target
table of the UPDATE statement.The initial value of the special register in a user-defined function or procedure is inherited from the invoking application. In other contexts the initial value of the special register is the null value.
The value of this special register can be changed by executing the SET CURRENT TEMPORAL BUSINESS_TIME statement.
The
setting of the CURRENT TEMPORAL BUSINESS_TIME special register impacts
the following compiled SQL objects when the associated package is
bound with the BUSTIMESENSITIVE bind option set to YES:
- SQL procedures
- Compiled functions
- Compiled triggers
- Compound SQL (compiled) statements
- External UDFs
For the following examples, assume the table IN_TRAY is an application-period temporal table.
Example 1: Based
on the state of the messages in IN_TRAY as of the date specified by
the CURRENT TEMPORAL BUSINESS_TIME special register, list the user
IDs and subject lines.
SELECT SOURCE, SUBJECT FROM IN_TRAY
Assuming
that the CURRENT TEMPORAL BUSINESS_TIME special register was previously
set to the value CURRENT TIMESTAMP-4 DAYS and is currently set to
the null value, the following statement returns the same result.SELECT SOURCE, SUBJECT
FROM IN_TRAY
FOR BUSINESS_TIME AS OF CURRENT TIMESTAMP-4 DAYS
Example
2: List the user ID and subject line for the messages in IN_TRAY
sent before the date specified by the CURRENT TEMPORAL BUSINESS_TIME
special register.
SELECT SOURCE, SUBJECT
FROM IN_TRAY
WHERE DATE(RECEIVED) < DATE(CURRENT TEMPORAL BUSINESS_TIME)
Assuming
that the CURRENT TEMPORAL BUSINESS_TIME special register was previously
set to '2011-01-01-00.00.00' and is currently set to the null value,
the following statement returns the same result.
SELECT SOURCE, SUBJECT
FROM IN_TRAY
FOR BUSINESS_TIME AS OF '2011-01-01-00.00.00'
WHERE DATE(RECEIVED) < DATE('2011-01-01-00.00.00')