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
The setting for the BUSTIMESENSITIVE bind option determines whether references to application-period temporal tables and bitemporal tables in both static SQL statements and dynamic SQL statements in a package are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. The bind option can be set to YES or NO.

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')