CURRENT TIMESTAMP special register

The CURRENT TIMESTAMP (or 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 application server.

If this special register is used more than once 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. It is possible for separate CURRENT TIMESTAMP special register requests to return the same value; if unique values are required, consider using the GENERATE_UNIQUE function, a sequence, or an identity column.

If a timestamp with a specific precision is desired, the special register can be referenced as CURRENT TIMESTAMP(integer), where integer can range from 0 to 12. The default precision is 6. The precision of the clock reading varies by platform and the resulting value is padded with zeros where the precision of the retrieved clock reading is less than the precision of the request.

For example:
  • CURRENT TIMESTAMP()
    • Output in Linux: 2015-03-23-09.41.24.684842
  • CURRENT TIMESTAMP(12)
    • Output in Linux: 2015-03-23-09.41.24.684842000000
  • CURRENT TIMESTAMP(6)
    • Output in Linux: 2015-03-23-09.41.24.684842
  • CURRENT TIMESTAMP(3)
    • Output in Linux: 2015-03-23-09.41.24.684

When used in an SQL statement inside a routine, CURRENT TIMESTAMP is not inherited from the invoking statement.

In a federated system, CURRENT TIMESTAMP can be used in a query intended for data sources. When the query is processed, the timestamp returned will be obtained from the CURRENT TIMESTAMP register at the federated server, not from the data sources.

SYSDATE can also be specified as a synonym for CURRENT TIMESTAMP(0).

LOCALTIMESTAMP can also be specified as a synonym for CURRENT TIMESTAMP.

LOCALTIMESTAMP(integer) can also be specified as a synonym for CURRENT TIMESTAMP(integer).

Example: 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)