CURRENT TEMPORAL SYSTEM_TIME special register

The CURRENT TEMPORAL SYSTEM_TIME special register specifies a TIMESTAMP(12) value that is used in the default SYSTEM_TIME period specification for references to system-period temporal tables.

When a system-period temporal table is referenced and the value in effect for the CURRENT TEMPORAL SYSTEM_TIME special register is represented by CTST, which is the non-null value, , the following period specification is implicit:

FOR SYSTEM_TIME AS OF CTST

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 SYSTEM_TIME statement.

The setting of the CURRENT TEMPORAL SYSTEM_TIME special register impacts the following compiled SQL objects when they have been bound with the SYSTIMESENSITIVE bind option set to YES:

SQL procedures
Compiled functions
Compiled triggers
Compound SQL (compiled) statements
External UDFs)

The setting for the SYSTIMESENSITIVE bind option determines whether references to system-period temporal tables in both static SQL statements and dynamic SQL statements in a package are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. The bind option can be set to YES or NO.

For the following examples, assume the table IN_TRAY is a system-period temporal table.

Example 1: Based on the state of the messages in IN_TRAY as of the date specified by the CURRENT TEMPORAL SYSTEM_TIME special register, list the user IDs and subject lines.

SELECT SOURCE, SUBJECT 
    FROM IN_TRAY

Assuming that the CURRENT TEMPORAL SYSTEM_TIME special register was previously set to the value CURRENT TIMESTAMP-7 DAYS and is currently set to the null value, the following statement returns the same result.

SELECT SOURCE, SUBJECT 
    FROM IN_TRAY 
    FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME

Example 2: List the user ID and subject line for the messages in IN_TRAY sent before the value specified by the CURRENT TEMPORAL SYSTEM_TIME special register.

SELECT SOURCE, SUBJECT 
    FROM IN_TRAY
    WHERE RECEIVED < CURRENT TEMPORAL SYSTEM_TIME

Assuming that the CURRENT TEMPORAL SYSTEM_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 SYSTEM_TIME AS OF '2011-01-01-00.00.00'
    WHERE DATE(RECEIVED) < DATE('2011-01-01-00.00.00')