CURRENT TIMESTAMP special register

The CURRENT TIMESTAMP special register specifies a timestamp that is based on a reading of the time-of-day clock when the SQL statement is executed at the current server.

If this special register is used more than one time within a single SQL statement, or used with CURRENT DATE or CURRENT TIME within a single statement, all values are based on a single clock reading.1

The value of CURRENT TIMESTAMP in a user-defined function or stored procedure is inherited according to the rules in Table 1.

Specifying CURRENT_TIMESTAMP is equivalent to specifying CURRENT TIMESTAMP.

If you want a timestamp with a specified precision, the special register can be referenced as CURRENT TIMESTAMP(integer), where integer can range 0–12. The default precision is 6. SYSDATE can also be specified as a synonym for CURRENT TIMESTAMP(0).

If you want a timestamp with a time zone, the special register can be referenced as CURRENT TIMESTAMP (integer) WITH TIME ZONE, or CURRENT TIMESTAMP WITH TIME ZONE. SYSTIMESTAMP can be specified as an alternative to CURRENT TIMESTAMP(12) WITH TIME ZONE. The time zone is determined from the CURRENT TIME ZONE special register.

Note: If the CURRENT TIMESTAMP special register is referenced in a timestamp with time zone context (for example, when compared with a timestamp with time zone column) the implicit time zone for the CURRENT TIMESTAMP special register will be based on the implicit time zone system parameter, which could be a different value from the CURRENT TIME ZONE special register. To avoid misinterpretation of the time zone in this case, CURRENT TIMESTAMP WITH TIME ZONE should be used.

Examples

Example 1
Display information about the full image copies that were taken in the last week. 
  SELECT * FROM SYSIBM.SYSCOPY
    WHERE TIMESTAMP > CURRENT TIMESTAMP - 7 DAYS;
Example 2
Insert a row into the IN_TRAY table. The value of the RECEIVED column should be a timestamp that indicates when the row was inserted. The values for the other three columns come from the host variables SRC (CHAR(8)), SUB (CHAR(64)), and TXT (VARCHAR(200)). 
INSERT INTO IN_TRAY 
	VALUES (CURRENT TIMESTAMP, :SRC, :SUB, :TXT)
Example 3
Retrieve the value of the CURRENT TIMESTAMP special register with a precision of 8 and include the time zone:
SELECT CURRENT TIMESTAMP(8) WITH TIME ZONE 
	FROM SYSIBM.SYSDUMMY1;
1 Except for the case of a non-atomic multiple row INSERT or MERGE statement.