DB2 datetime and temporal special registers

The clock registers

CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP are stored internally as datetime values. When two or more of them appear — explicitly or as a column default — in one SQL statement, they represent the same point in time. Db2 takes one time-of-day clock reading, adds the TIMEZONE parameter from SYS1.PARMLIB(CLOCKxx), and treats the result as local date/time/timestamp at the server that executes the statement.

The documented exception is a non-atomic multiple-row INSERT or MERGE, where rows can see different readings.

You cannot SET these three registers. z/OS SET CLOCK and SET DATE can move the underlying clock and therefore the register values. Distributed SQL evaluates them at the server, not at the requester.

Inside a stored procedure or UDF, each SQL statement normally gets a new clock reading. If that routine runs in the scope of a trigger, Db2 uses the timestamp of the triggering statement instead, so trigger actions stay aligned with the INSERT or UPDATE that fired them.

CURRENT DATE

CURRENT DATE (also CURRENT_DATE) is a DATE from that single clock reading at the current server.

1
2
3
4
5
6
SELECT AVG(YEAR(CURRENT DATE - BIRTHDATE))
  FROM DSN8C10.EMP;
 
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE HIREDATE <= CURRENT DATE;

Prefer a range on the column (HIREDATE BETWEEN … AND CURRENT DATE) when you can keep the predicate indexable. Wrapping the column in YEAR(HIREDATE) = YEAR(CURRENT DATE) is harder on access paths.

CURRENT TIME

CURRENT TIME (also CURRENT_TIME) is a TIME from the same clock reading. Combining it with CURRENT DATE in one SELECT gives a consistent “now” split across two columns.

1
2
SELECT DSN8C10.PROJACT.*, CURRENT DATE, CURRENT TIME
  FROM DSN8C10.PROJACT;

CURRENT TIMESTAMP

CURRENT TIMESTAMP (also CURRENT_TIMESTAMP) is a timestamp from the same clock. Default precision is 6. You can request another precision:

• CURRENT TIMESTAMP(integer) — integer from 0 through 12
• SYSDATE — synonym for CURRENT TIMESTAMP(0)
• CURRENT TIMESTAMP WITH TIME ZONE or CURRENT TIMESTAMP(integer) WITH TIME ZONE — includes a time zone from CURRENT TIME ZONE
• SYSTIMESTAMP — alternative for CURRENT TIMESTAMP(12) WITH TIME ZONE

1
2
3
4
5
6
7
8
SELECT * FROM SYSIBM.SYSCOPY
 WHERE TIMESTAMP > CURRENT TIMESTAMP - 7 DAYS;
 
INSERT INTO IN_TRAY
  VALUES (CURRENT TIMESTAMP, :SRC, :SUB, :TXT);
 
SELECT CURRENT TIMESTAMP(8) WITH TIME ZONE
  FROM SYSIBM.SYSDUMMY1;

If you compare CURRENT TIMESTAMP (without WITH TIME ZONE) to a TIMESTAMP WITH TIME ZONE column, the implicit zone comes from the implicit time zone system parameter, which can differ from CURRENT TIME ZONE. Use CURRENT TIMESTAMP WITH TIME ZONE when the context is zoned.

Do not use CURRENT TIMESTAMP as the only uniqueness scheme for a multi-row INSERT. Every row of that one statement can receive the same value. Sequences, identity columns, and GENERATE_UNIQUE exist for that job.

CURRENT TIME ZONE

CURRENT TIME ZONE (also CURRENT TIMEZONE and CURRENT_TIMEZONE) is the difference between UTC and local time as defined by the current server, if SESSION TIME ZONE has not been set. Local difference comes from z/OS CLOCKxx TIMEZONE. Data type DECIMAL(6,0): a time duration — two digits hours, two minutes, two seconds. Hours are adjusted to fit between −24 and 24 exclusive.

Subtracting CURRENT TIME ZONE from a local time converts that local time to UTC.

1
2
SELECT RECEIVED - CURRENT TIME ZONE, SOURCE, SUBJECT, NOTE_TEXT
  FROM IN_TRAY;

SESSION TIME ZONE is a different register (VARCHAR(128), form ±th:tm, SET SESSION TIME ZONE). After you SET it, SESSION TIME ZONE and CURRENT TIME ZONE might no longer describe the same offset. CURRENT TIMESTAMP WITH TIME ZONE still takes the zone from CURRENT TIME ZONE per the SQL Reference.

CURRENT TEMPORAL SYSTEM_TIME

CURRENT TEMPORAL SYSTEM_TIME is a TIMESTAMP(12) used as the default SYSTEM_TIME period for system-period temporal tables. Initial value is null in ordinary application SQL (inherited from the invoker in a trigger or an INHERIT SPECIAL REGISTERS routine; null under DEFAULT SPECIAL REGISTERS).

When the register is not null and a query references a system-period temporal table, Db2 implies:

1
FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME

With SYSTIMESENSITIVE YES, you cannot also write FOR SYSTEM_TIME explicitly on that select-statement. SET CURRENT TEMPORAL SYSTEM_TIME is not a committable operation; ROLLBACK does not restore the old value. A SET inside a routine is not passed back to the caller.

1
2
3
4
5
6
SET CURRENT TEMPORAL SYSTEM_TIME =
    TIMESTAMP('2008-01-01') + 5 DAYS;
SET CURRENT TEMPORAL SYSTEM_TIME = NULL;
 
SELECT * FROM STT WHERE POLICY_ID = 123;
-- equivalent to FOR SYSTEM_TIME AS OF current register when not null

CURRENT TEMPORAL BUSINESS_TIME

CURRENT TEMPORAL BUSINESS_TIME is the application-period counterpart: TIMESTAMP(12), initially null, SET with SET CURRENT TEMPORAL BUSINESS_TIME.

• If BUSINESS_TIME columns are TIMESTAMP, the implicit period is FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME
• If they are DATE, Db2 uses FOR BUSINESS_TIME AS OF CAST(CURRENT TEMPORAL BUSINESS_TIME AS DATE)

BUSTIMESENSITIVE YES is the bind option that lets this implicit period apply. The same nested-routine rule holds: SET inside a procedure does not change the caller’s register.

1
2
3
4
5
6
SET CURRENT TEMPORAL BUSINESS_TIME =
    TIMESTAMP('2011-01-01') + 5 DAYS;
 
SELECT SOURCE, SUBJECT
  FROM IN_TRAY
 WHERE DATE(CURRENT TEMPORAL BUSINESS_TIME) = DATE(RECEIVED);

CURRENT LOCK TIMEOUT

CURRENT LOCK TIMEOUT is not a datetime value. It is grouped here because the special-register checklist places it with the temporal family. It arrived in Db2 13 function level 500 so an application can override the subsystem IRLM wait (IRLMRWT, often 30 seconds) without a ZPARM change.

Data type INTEGER, seconds. SET CURRENT LOCK TIMEOUT accepts:

• NULL or default — use IRLMRWT; a SELECT of the register returns that IRLMRWT value
• WAIT or −1 — do not time out (wait until the lock is granted or a deadlock is detected)
• NOT WAIT or 0 — if the lock is not available now, return an error immediately (no DSNT376I / IFCID 196 wait-timeout story)
• Integer 1–32767 — wait that many seconds

Subsystem parameter SPREG_LOCK_TIMEOUT_MAX can cap how large a value an application is allowed to SET (−1 means no extra cap). CURRENT LOCK TIMEOUT also applies to similar waits such as claims and drains. It does not apply to P-locks or plan/package allocation locks.

1
2
3
SET CURRENT LOCK TIMEOUT = 50;
SET CURRENT LOCK TIMEOUT = NOT WAIT;
SET CURRENT LOCK TIMEOUT = NULL;

Profile tables can set this register for local as well as DDF applications starting in Db2 13 — unlike many other special-register profile attributes that remain DDF-oriented.

Explain It Like I'm Five

CURRENT DATE is “what day the big wall clock shows in the computer room.” CURRENT TIME is the time on that same clock. CURRENT TIMESTAMP is both written together. If you ask for the date and the timestamp in one sentence, Db2 looks at the clock once so the two answers cannot disagree. CURRENT TIME ZONE is how many hours that clock is ahead of or behind world UTC time. The temporal registers are a sticky note that says “pretend today is this old date” when you read history tables. CURRENT LOCK TIMEOUT is how long you are willing to wait in line for a toy someone else is holding — zero means you walk away at once, minus one means you wait until they finish or the teacher (deadlock) breaks it up.

Exercises

1. Select CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, and CURRENT TIME ZONE from SYSIBM.SYSDUMMY1. Confirm the date matches the date part of the timestamp.
2. Insert two rows in one INSERT … VALUES (CURRENT TIMESTAMP), (CURRENT TIMESTAMP) or a two-row SELECT INSERT and see whether the timestamps match.
3. Convert a TIMESTAMP WITHOUT TIME ZONE column to UTC with RECEIVED - CURRENT TIME ZONE.
4. On a system-period temporal table (or a sample), SET CURRENT TEMPORAL SYSTEM_TIME to a past timestamp, SELECT without FOR SYSTEM_TIME, then SET the register back to NULL.
5. If you are on Db2 13 FL 500 or later, SELECT CURRENT LOCK TIMEOUT, SET it to 5, and discuss with operations whether NOT WAIT is safe for your transaction.