Db2 temporal and date-range patterns

Start by naming the kind of time

A column named START_DATE does not explain its meaning. It might be when an order was entered, when a policy became legally effective, when a row version reached Db2, or the local date shown to a customer. Those meanings produce different answers. Before writing SQL, identify the time axis and the expected boundary behavior.

• An instant is one point, such as EVENT_TS or PAYMENT_RECEIVED_TS.
• A calendar value is a DATE such as invoice due date. It has no time of day or time-zone offset.
• A business-valid period says when a price, policy, or assignment is considered true in the business domain.
• A system period says when Db2 held a particular version of a row.

IBM recommends using Db2 DATE, TIME, and TIMESTAMP types rather than storing datetime values as unrelated numbers or strings. Native types validate values, support datetime arithmetic, and communicate intent to the optimizer. Use DATE only when a day is the complete fact. Use TIMESTAMP when ordering within a day matters. A timestamp can have fractional-second precision from 0 through 12; the default precision is 6.

Half-open ranges remove boundary ambiguity

The most reusable interval model is [start, end): include the start, exclude the end. A reservation from 09:00 to 10:00 contains 09:00 but not 10:00. Another reservation can begin exactly at 10:00 without colliding. A January price begins on January 1 and ends on February 1, rather than pretending to end at the last representable instant of January 31.

1
2
3
4
5
6
-- All events on 15 August 2026
SELECT EVENT_ID, EVENT_TS, EVENT_TYPE
  FROM APP.EVENT_LOG
 WHERE EVENT_TS >= TIMESTAMP('2026-08-15-00.00.00')
   AND EVENT_TS <  TIMESTAMP('2026-08-16-00.00.00')
 ORDER BY EVENT_TS;

This query remains correct if EVENT_TS has microseconds, nanoseconds, or greater precision. In contrast, an upper boundary of 23:59:59 misses values such as 23:59:59.500000. Appending a string of 9s merely couples the query to a guessed column precision. The next-day exclusive boundary expresses the business requirement directly.

Why ordinary BETWEEN is often the wrong day filter

Ordinary SQL BETWEEN is inclusive at both ends. It is fine when both endpoints genuinely belong in the answer—for example, DATE values from the first through the last due date. It is risky for adjacent TIMESTAMP windows. If one query ends at midnight and the next begins at midnight, inclusive endpoints can count that boundary twice. Prefer explicit greater-than-or-equal and less-than predicates when representing a half-open window.

1
2
3
4
5
6
7
8
9
10
-- Inclusive DATE requirement: both dates are intended
SELECT INVOICE_ID, DUE_DATE
  FROM AR.INVOICE
 WHERE DUE_DATE BETWEEN DATE('2026-08-01') AND DATE('2026-08-31');
 
-- Equivalent half-open DATE form, easier to compose with adjacent months
SELECT INVOICE_ID, DUE_DATE
  FROM AR.INVOICE
 WHERE DUE_DATE >= DATE('2026-08-01')
   AND DUE_DATE <  DATE('2026-09-01');

Parameterize boundaries instead of building strings

Application code should bind typed host variables or parameter markers for the lower and upper bounds. Compute “next day” or “next month” once, outside the column expression. That avoids locale-dependent string parsing and keeps SQL reusable. Db2 date arithmetic understands calendar rules, including month lengths and leap years, but month-end adjustment can occur when a target month lacks the original day. Test month arithmetic around January 31 and February rather than assuming every month behaves like 30 days.

1
2
3
4
5
6
7
8
9
-- Host variables are typed as TIMESTAMP
SELECT ORDER_ID, CREATED_TS
  FROM SALES.ORDERS
 WHERE CREATED_TS >= :MONTH_START
   AND CREATED_TS <  :NEXT_MONTH_START;
 
-- Db2 calendar arithmetic examples
VALUES DATE('2024-02-28') + 1 DAY;
VALUES ADD_DAYS(DATE('2024-02-28'), 1);

Testing whether two ranges overlap

Suppose each booking stores START_TS and END_TS as a valid half-open range. Two ranges overlap exactly when each begins before the other ends. The compact formula handles all shapes: one interval inside another, partial overlap from either side, and identical periods.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Generic half-open overlap test
A.START_TS < B.END_TS
AND B.START_TS < A.END_TS
 
-- Find conflicting reservations for the same room
SELECT A.ROOM_ID,
       A.RESERVATION_ID AS FIRST_ID,
       B.RESERVATION_ID AS SECOND_ID
  FROM HOTEL.RESERVATION A
  JOIN HOTEL.RESERVATION B
    ON B.ROOM_ID = A.ROOM_ID
   AND B.RESERVATION_ID > A.RESERVATION_ID
   AND A.START_TS < B.END_TS
   AND B.START_TS < A.END_TS;

The greater-ID condition reports each pair once and prevents a row from matching itself. Notice that [09:00, 10:00) and [10:00, 11:00) do not overlap: both strict less-than tests are not true. If your documented model includes both endpoints, touching at 10:00 might count as an overlap and the operators must change. Do not change operators casually; they encode the interval definition.

Non-overlap, containment, and point-in-time tests

• Half-open ranges do not overlap when A.END_TS <= B.START_TS or B.END_TS <= A.START_TS.
• Range B is contained in A when A.START_TS <= B.START_TS and B.END_TS <= A.END_TS.
• An instant belongs to A when A.START_TS <= :POINT_TS and :POINT_TS < A.END_TS.
• A valid non-empty half-open range requires START_TS < END_TS. Enforce that rule in DDL when the table is not using a Db2 period definition that already enforces it.

1
2
3
4
5
6
7
8
CREATE TABLE HOTEL.RESERVATION (
  RESERVATION_ID  BIGINT NOT NULL,
  ROOM_ID         INTEGER NOT NULL,
  START_TS        TIMESTAMP(6) NOT NULL,
  END_TS          TIMESTAMP(6) NOT NULL,
  PRIMARY KEY (RESERVATION_ID),
  CONSTRAINT VALID_RESERVATION_RANGE CHECK (START_TS < END_TS)
);

Detecting gaps with window functions

A gap is a boundary problem between consecutive rows. For each business key, order rows by the beginning of the period and compare the current start with the previous end. LAG supplies that previous value without a self-join. If periods are already known not to overlap, CURRENT_START > PREVIOUS_END means a gap and equality means perfect adjacency.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH ORDERED_PRICE AS (
  SELECT PRODUCT_ID,
         VALID_FROM,
         VALID_TO,
         PRICE,
         LAG(VALID_TO) OVER (
           PARTITION BY PRODUCT_ID
           ORDER BY VALID_FROM, VALID_TO
         ) AS PREVIOUS_TO
    FROM SALES.PRODUCT_PRICE
)
SELECT PRODUCT_ID,
       PREVIOUS_TO AS GAP_START,
       VALID_FROM  AS GAP_END
  FROM ORDERED_PRICE
 WHERE PREVIOUS_TO IS NOT NULL
   AND VALID_FROM > PREVIOUS_TO
 ORDER BY PRODUCT_ID, GAP_START;

A simple LAG of the immediately preceding end can misclassify data when overlapping rows are allowed and an earlier, longer row extends beyond that end. In that case compare the current start with the running maximum of all previous end values. The running maximum represents the furthest coverage reached so far.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH COVERAGE AS (
  SELECT PRODUCT_ID,
         VALID_FROM,
         VALID_TO,
         MAX(VALID_TO) OVER (
           PARTITION BY PRODUCT_ID
           ORDER BY VALID_FROM, VALID_TO
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
         ) AS COVERED_THROUGH
    FROM SALES.PRODUCT_PRICE
)
SELECT PRODUCT_ID, COVERED_THROUGH AS GAP_START, VALID_FROM AS GAP_END
  FROM COVERAGE
 WHERE COVERED_THROUGH IS NOT NULL
   AND VALID_FROM > COVERED_THROUGH;

Keep datetime predicates sargable

A sargable predicate gives Db2 a useful search argument for an access path. The clearest pattern puts the indexed datetime column by itself on one side and a constant, host variable, parameter marker, or compatible non-column expression on the other. Wrapping the column in DATE, CHAR, YEAR, or arithmetic can change predicate classification and prevent a matching index scan. Db2 can optimize some function forms and expression-based indexes exist for specialized designs, so verify with EXPLAIN; do not assume every function is always fatal. Still, the bare-column range is the reliable default.

1
2
3
4
5
6
7
8
9
10
-- Less desirable: function applied for every candidate row
SELECT EVENT_ID
  FROM APP.EVENT_LOG
 WHERE DATE(EVENT_TS) = :EVENT_DATE;
 
-- Preferred search range
SELECT EVENT_ID
  FROM APP.EVENT_LOG
 WHERE EVENT_TS >= TIMESTAMP(:EVENT_DATE)
   AND EVENT_TS <  TIMESTAMP(:EVENT_DATE + 1 DAY);

Index order should follow the workload. If every lookup supplies CUSTOMER_ID and a time range, an index beginning (CUSTOMER_ID, EVENT_TS) can support equality on the customer followed by range matching on time. An index beginning with EVENT_TS may be better for cross-customer chronological reports. There is no universal date index: collect statistics, run EXPLAIN, and compare the actual filtering pattern.

Watch for implicit casts

Comparing a TIMESTAMP column with a character value asks Db2 to perform conversion. Conversion rules, application encoding, and malformed values can introduce surprises. Bind a TIMESTAMP value for a TIMESTAMP column and a DATE value for a DATE column. Period specifications also restrict precision: an expression must not have greater timestamp precision than the columns that define the period. Use an explicit CAST when a deliberate precision reduction is required.

BUSINESS_TIME for valid-date ranges

Hand-written start and end columns are appropriate for many tables. When effective-dated data is central to the design, an application-period temporal table gives those columns formal meaning. PERIOD BUSINESS_TIME declares the begin and end columns. The application supplies their values because they describe the business calendar. Db2 can also enforce non-overlap for a logical key through a unique constraint or unique index with BUSINESS_TIME WITHOUT OVERLAPS.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE SALES.PRODUCT_PRICE (
  PRODUCT_ID  INTEGER NOT NULL,
  PRICE       DECIMAL(11,2) NOT NULL,
  VALID_FROM  DATE NOT NULL,
  VALID_TO    DATE NOT NULL,
  PERIOD BUSINESS_TIME (VALID_FROM, VALID_TO),
  PRIMARY KEY (PRODUCT_ID, BUSINESS_TIME WITHOUT OVERLAPS)
);
 
INSERT INTO SALES.PRODUCT_PRICE
VALUES (100, 19.95, DATE('2026-01-01'), DATE('2026-04-01'));
 
INSERT INTO SALES.PRODUCT_PRICE
VALUES (100, 21.50, DATE('2026-04-01'), DATE('2026-07-01'));

The two rows are adjacent, not overlapping, under the common inclusive-start, exclusive-end model. Without the WITHOUT OVERLAPS key, PERIOD BUSINESS_TIME ensures a valid period but does not by itself guarantee that one product has only one active price at an instant. Use database-enforced integrity when overlap would violate the business rule; a pre-insert SELECT alone has a concurrency window.

1
2
3
4
5
6
7
8
9
10
11
-- Price in force at one business date
SELECT PRODUCT_ID, PRICE
  FROM SALES.PRODUCT_PRICE
  FOR BUSINESS_TIME AS OF DATE('2026-05-15')
 WHERE PRODUCT_ID = 100;
 
-- Versions that overlap a business window
SELECT PRODUCT_ID, PRICE, VALID_FROM, VALID_TO
  FROM SALES.PRODUCT_PRICE
  FOR BUSINESS_TIME FROM DATE('2026-03-01') TO DATE('2026-06-01')
 WHERE PRODUCT_ID = 100;

IBM documents AS OF, FROM value1 TO value2, and BETWEEN value1 AND value2 period specifications. FROM/TO is especially natural for half-open windows. BETWEEN has endpoint behavior defined by the period specification and period type, so it should not be treated as interchangeable punctuation. Choose the form whose documented semantics match the question.

SYSTEM_TIME for row-version history

System-period temporal data answers a different question: “What version did Db2 store at this earlier system time?” A system-period table uses TIMESTAMP(12) generated row-begin and row-end columns, plus a generated transaction-start-ID column. After a compatible history table exists, ADD VERSIONING connects it to the base table. Updates and deletes then preserve prior versions in history.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE HR.EMPLOYEE (
  EMP_ID       INTEGER NOT NULL PRIMARY KEY,
  DEPT_CODE    CHAR(3) NOT NULL,
  SYS_START    TIMESTAMP(12) NOT NULL
               GENERATED ALWAYS AS ROW BEGIN,
  SYS_END      TIMESTAMP(12) NOT NULL
               GENERATED ALWAYS AS ROW END,
  TRANS_START  TIMESTAMP(12)
               GENERATED ALWAYS AS TRANSACTION START ID,
  PERIOD SYSTEM_TIME (SYS_START, SYS_END)
);
 
CREATE TABLE HR.EMPLOYEE_HISTORY LIKE HR.EMPLOYEE;
 
ALTER TABLE HR.EMPLOYEE
  ADD VERSIONING USE HISTORY TABLE HR.EMPLOYEE_HISTORY;

Applications normally insert and update EMP_ID and DEPT_CODE, not the generated system columns. A temporal query against the base table lets Db2 search the appropriate current and history versions. AS OF selects versions whose system period contains one timestamp. FROM/TO selects versions that overlap a window.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- What did Db2 show at this system timestamp?
SELECT EMP_ID, DEPT_CODE
  FROM HR.EMPLOYEE
  FOR SYSTEM_TIME AS OF
      TIMESTAMP('2026-03-31-23.59.59.000000000000')
 WHERE EMP_ID = 42;
 
-- Which versions existed during this half-open system-time window?
SELECT EMP_ID, DEPT_CODE, SYS_START, SYS_END
  FROM HR.EMPLOYEE
  FOR SYSTEM_TIME
      FROM TIMESTAMP('2026-03-01-00.00.00.000000000000')
        TO TIMESTAMP('2026-04-01-00.00.00.000000000000')
 WHERE EMP_ID = 42;

Bitemporal questions use two clocks

A bitemporal table has both BUSINESS_TIME and SYSTEM_TIME. It can answer “What price is valid for May 15 according to what the database knew on March 31?” The business date and system timestamp are intentionally different. This is useful when a fact is corrected after the fact: BUSINESS_TIME stores when the corrected fact should apply, while SYSTEM_TIME preserves when the old and corrected versions were recorded.

1
2
3
4
5
SELECT POLICY_ID, COVERAGE_AMOUNT
  FROM INSURANCE.POLICY
  FOR SYSTEM_TIME AS OF :SYSTEM_AS_OF
  FOR BUSINESS_TIME AS OF :BUSINESS_AS_OF
 WHERE POLICY_ID = :POLICY_ID;

Each period name can appear at most once for a table reference. Db2 temporal special registers can also apply AS OF behavior implicitly when the package's temporal sensitivity bind options allow it. That is convenient for a consistent month-end report, but surprising in pooled sessions if the register is not restored. Make temporal register ownership and reset behavior part of the application contract.

Time zones, daylight saving, and local days

A TIMESTAMP WITHOUT TIME ZONE is a wall-clock value with no offset. It cannot tell whether 2026-11-01 01:30 occurred before or after a daylight-saving fallback. A TIMESTAMP WITH TIME ZONE carries an offset and represents an instant relative to UTC. Choose based on the fact being stored, not on display preference.

• Store an instant consistently—often as UTC or as TIMESTAMP WITH TIME ZONE—and convert for display at the edge of the application.
• Keep local civil values when the local clock itself is the business fact, such as “the branch opens at 09:00 local time.”
• Preserve a zone identifier outside the timestamp when future daylight-saving rules matter; an offset such as -05:00 is not the same thing as “America/New_York.”
• To query a user's local calendar day against UTC data, calculate that day's UTC start and next-day UTC start using the correct zone rules. The elapsed interval might be 23, 24, or 25 hours.

Do not add a fixed 24 hours to model every local next day. Resolve the next local midnight in the user's zone, then convert both boundaries to the stored timeline. Keep this conversion consistent across Java, COBOL, middleware, and SQL so two layers do not apply an offset twice.

Precision and sentinel-value caveats

Db2 supports timestamp fractional precision from 0 to 12. A host variable, literal, cast, period column, and comparison do not automatically share the same declared precision. Truncating a high-precision input can move it onto a boundary; rounding can move it across one. Define precision deliberately and test rows exactly at START, exactly at END, one representable unit before END, and one unit after START.

Open-ended business periods are often stored with a far-future end such as 9999-12-31. That convention works only when every writer and reader treats it consistently. It can complicate arithmetic that adds a day and can create a highly skewed index value. NULL is another option for a hand-built model, but every overlap predicate then needs explicit NULL handling. Db2 period definitions require concrete begin and end values, so use the documented maximum for that temporal feature and avoid arithmetic beyond it.

A practical range-design checklist

• Define whether each endpoint is inclusive or exclusive in the data contract.
• Use DATE for true calendar dates and TIMESTAMP for instants within a day.
• Adopt [start, end) unless the business has a clear reason for another model.
• Reject empty or reversed ranges with a constraint or a period definition.
• Use the two-sided overlap formula; do not test only whether one start is inside.
• Use next-boundary searches instead of guessing a final second or fraction.
• Compare the bare indexed column with typed bounds and inspect EXPLAIN output.
• Use BUSINESS_TIME WITHOUT OVERLAPS when non-overlap is an integrity rule.
• Use SYSTEM_TIME for database history, not as a substitute for business validity.
• Document time-zone conversion, fractional precision, and open-ended conventions.

Explain It Like I'm Five

Imagine colored strips laid on a ruler. A blue strip starts at 1 and stops just before 4. A green strip can start exactly at 4, so there is no double color and no empty space. That is a half-open range. Two strips overlap when each starts before the other one ends. BUSINESS_TIME writes “this price sticker is good from spring until summer.” SYSTEM_TIME is a camera that remembers when the sticker was attached or replaced. Db2 can use both: one clock says when the sticker is supposed to be true, and the other says when the database knew about it.

Exercises

• Rewrite a query that uses DATE(EVENT_TS) = :DAY as a half-open TIMESTAMP range. Explain why the rewritten predicate can be friendlier to an index.
• Insert [09:00, 10:00), [10:00, 11:00), and [09:30, 10:30) reservations. Use the two-sided overlap join and predict which pairs it returns.
• Build an effective-dated PRODUCT_PRICE sample with one gap and one overlap. Use LAG to find the gap, then explain when the running-MAX version is safer.
• Create a BUSINESS_TIME table with a WITHOUT OVERLAPS key. Try two adjacent periods and then an overlapping period for the same key. Record the difference.
• Write one FOR SYSTEM_TIME AS OF query and one FOR BUSINESS_TIME AS OF query. Describe the different question answered by each.
• Choose a daylight-saving transition in your users' zone. Calculate the UTC start and end of that local day and verify whether its duration is 23, 24, or 25 hours.

Frequently asked questions