DB2 10.5 for Linux, UNIX, and Windows

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 Windows: 2015-03-23-09.41.24.684000
- Output in Linux: 2015-03-23-09.41.24.684842
- Output in UNIX: 2015-03-23-09.41.24.684842

CURRENT TIMESTAMP(12)
- Output in Windows: 2015-03-23-09.41.24.684000000000
- Output in Linux: 2015-03-23-09.41.24.684842000000
- Output in UNIX: 2015-03-23-09.41.24.684842000000

CURRENT TIMESTAMP(6)
- Output in Windows: 2015-03-23-09.41.24.684000
- Output in Linux: 2015-03-23-09.41.24.684842
- Output in UNIX: 2015-03-23-09.41.24.684842

CURRENT TIMESTAMP(3)
- Output in Windows: 2015-03-23-09.41.24.684
- Output in Linux: 2015-03-23-09.41.24.684
- Output in UNIX: 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.

On a DB2® pureScale® instance, with transaction workload balancing enabled, the CURRENT TIMESTAMP special register does not necessarily return increasing values across transactions if those transactions are executed on different members.

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

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)