Db2 TIMESTAMP data type

Timestamp components and precision

A TIMESTAMP is a seven-part value in the usual teaching model: year, month, day, hour, minute, second, and fractional seconds. IBM documents the overall range from 0001-01-01-00.00.00.000000000 through 9999-12-31-24.00.00.000000000 with nanosecond precision available at the type’s maximum. Timestamps can also hold time zone information when you use the WITH TIME ZONE form.

Declare precision explicitly when it matters: TIMESTAMP(p) with p from 0 to 12. If you omit (p), Db2 uses a default precision of 6 (microseconds)—a fact worth remembering when comparing to systems that default to 0 or 3. Higher precision costs a few more storage bytes:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE APP.AUDIT_LOG (
    EVENT_ID     BIGINT NOT NULL,
    EVENT_TS     TIMESTAMP(6) NOT NULL,
    EVENT_MSG    VARCHAR(200),
    PRIMARY KEY (EVENT_ID)
);
 
CREATE TABLE APP.MARKET_TICK (
    SYMBOL       CHAR(8) NOT NULL,
    TICK_TS      TIMESTAMP(9) NOT NULL,
    PRICE        DECIMAL(11, 4) NOT NULL
);

Timestamp literals

Timestamp constants appear as strings in valid formats or as typed constants. Fractional digits in the literal influence precision. Details and format tables live on the datetime literals page; here are everyday patterns:

1
2
3
4
5
6
7
8
9
10
INSERT INTO APP.AUDIT_LOG (EVENT_ID, EVENT_TS, EVENT_MSG)
VALUES (1, TIMESTAMP '2024-03-15-14.30.00.000000', 'ROW INSERTED');
 
SELECT EVENT_ID, EVENT_TS
  FROM APP.AUDIT_LOG
 WHERE EVENT_TS >= TIMESTAMP '2024-03-15-00.00.00';
 
-- “Now” via special register:
INSERT INTO APP.AUDIT_LOG (EVENT_ID, EVENT_TS, EVENT_MSG)
VALUES (2, CURRENT TIMESTAMP, 'AUTO STAMP');

CURRENT TIMESTAMP (and related registers such as CURRENT TIMESTAMP WITH TIME ZONE where applicable) give you the statement’s notion of now. Use them for defaults and audit columns; use literals when replaying a fixed instant in tests.

TIMESTAMP vs DATE / TIME

A common design smell is three columns—business DATE, TIME, and a CHAR “timezone”—that never quite agree. If users ask “when did this happen?”, start with TIMESTAMP (or WITH TIME ZONE). If users ask “on which business day did this count?”, a DATE (perhaps derived from a timestamp) may still be required for cutover rules.

1
2
3
4
5
6
7
8
9
10
11
-- Extract pieces when you need them:
SELECT EVENT_ID,
       DATE(EVENT_TS) AS EVENT_DATE,
       TIME(EVENT_TS) AS EVENT_TIME,
       YEAR(EVENT_TS) AS EVENT_YEAR
  FROM APP.AUDIT_LOG;
 
-- Ordering events is natural with TIMESTAMP:
SELECT EVENT_ID, EVENT_TS, EVENT_MSG
  FROM APP.AUDIT_LOG
 ORDER BY EVENT_TS DESC;

Time zone intro

TIMESTAMP WITHOUT TIME ZONE (the usual default people mean by TIMESTAMP) stores a local-style timestamp without an attached offset. That is fine when the whole enterprise agrees on “Db2 time” or a single region. It becomes ambiguous when the same literal clock reading means different instants in New York and Paris.

TIMESTAMP WITH TIME ZONE adds zone/offset information to the value. Literals can include offsets such as +05:00, short forms, or Z for UTC, subject to documented format rules. Storage uses a couple of extra bytes versus WITHOUT TIME ZONE. Applications, drivers, and display layers must understand zones—otherwise you only moved the confusion to the client.

1
2
3
4
5
6
7
8
9
-- Illustrative WITH TIME ZONE column
CREATE TABLE APP.GLOBAL_EVENT (
    EVENT_ID   BIGINT NOT NULL,
    EVENT_TSTZ TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    PRIMARY KEY (EVENT_ID)
);
 
-- Literal forms with offsets are covered on the literals page;
-- design reviews should confirm driver support and display rules.

Practical advice

• Default to TIMESTAMP(6) for audit/event columns unless standards say otherwise
• Raise precision only when the business truly needs it (market data, tech telemetry)
• Use WITH TIME ZONE when cross-region correctness is a requirement—not as cargo cult
• Do not store timestamps as CHAR if you can avoid it—validation and ordering suffer

Explain It Like I'm Five

A TIMESTAMP is a sticker that says both the day and the exact time something happened—like “March 15, 2024 at 2:30 and some tiny fractions of a second.” DATE only has the day. TIME only has the clock. A special TIMESTAMP can also say which time-zone neighborhood the clock was in, so “3:00” in one place is not confused with “3:00” somewhere else.

Exercises

1. Declare TIMESTAMP, TIMESTAMP(0), and TIMESTAMP(9) columns and explain when each fits.
2. Rewrite a design that uses CHAR(26) for events as TIMESTAMP—list two benefits.
3. When would you keep a separate business DATE column even if EVENT_TS exists?
4. Explain WITHOUT TIME ZONE vs WITH TIME ZONE to an application teammate in four sentences.
5. Compute the storage byte count for TIMESTAMP(6) WITHOUT TIME ZONE using IBM’s formula.