Db2 TIME data type

Internal format and range

A TIME is a three-part value—hour, minute, and second—for a time of day on a 24-hour clock. Hours run from 0 through 24; minutes and seconds from 0 through 59. The documented range is 00.00.00 through 24.00.00. The value 24.00.00 is Db2’s way of representing the end of the day in this type’s model—something to remember when validating inputs from systems that only allow 00:00:00–23:59:59.

Internally, TIME occupies 3 bytes. Like DATE, applications usually see an external character form when fetching into host variables. Catalog and SQLDA lengths differ between internal storage and external string length—DCLGEN follows your install’s local time length settings when they apply.

1
2
3
4
5
6
CREATE TABLE APP.STORE_HOURS (
    STORE_ID    CHAR(4) NOT NULL,
    OPEN_TIME   TIME NOT NULL,
    CLOSE_TIME  TIME NOT NULL,
    PRIMARY KEY (STORE_ID)
);

Time literals

Time constants are written as strings in recognized formats or as typed constants:

1
2
3
4
5
6
INSERT INTO APP.STORE_HOURS (STORE_ID, OPEN_TIME, CLOSE_TIME)
VALUES ('S100', TIME '09:00:00', TIME '21:00:00');
 
SELECT STORE_ID
  FROM APP.STORE_HOURS
 WHERE OPEN_TIME <= TIME '10:00:00';

Format variants (with dots, colons, or locale styles) depend on defaults—see the datetime literals page. Prefer explicit TIME '...' forms in shared SQL so readers are not guessing whether '10.30.00' is local-preferred.

CURRENT TIME

CURRENT TIME returns the current time-of-day special register when the statement executes. It does not include the date; pair with CURRENT DATE or use CURRENT TIMESTAMP when you need a full instant.

1
2
3
SELECT STORE_ID, OPEN_TIME, CLOSE_TIME
  FROM APP.STORE_HOURS
 WHERE CURRENT TIME BETWEEN OPEN_TIME AND CLOSE_TIME;

Precision considerations

TIME stops at whole seconds. There is no TIME(3) for milliseconds. If a trading system needs subsecond event times, use TIMESTAMP (optionally with an explicit precision) or another design approved by your DBA standards—not a CHAR clock string you parse by hand.

Arithmetic with time durations exists, but crossing midnight and combining DATE+TIME into a TIMESTAMP are design moments—spell them out in SQL with TIMESTAMP constructors or explicit casts rather than hoping string concatenation produces valid timestamps.

1
2
3
4
5
6
SELECT STORE_ID,
       HOUR(OPEN_TIME)   AS OPEN_HH,
       MINUTE(OPEN_TIME) AS OPEN_MM,
       SECOND(OPEN_TIME) AS OPEN_SS,
       CHAR(OPEN_TIME)   AS OPEN_CHAR
  FROM APP.STORE_HOURS;

Host-variable / string conversion gotchas

• AM/PM vs 24-hour — external formats may allow 12-hour styles; be consistent
• Separators — colons versus dots; match the expected pattern
• 24.00.00 — valid in Db2 TIME; some UIs reject hour 24
• Implicit CHAR comparisons — string sort order is not always the same as time logic if formats vary
• Null TIME — remember indicators on FETCH for nullable columns

Keep columns as TIME, convert at the edges, and document the external format your COBOL/Java services use. When events are “on a day at a time,” a single TIMESTAMP column is often clearer than separate DATE and TIME columns that you must keep in sync.

Explain It Like I'm Five

TIME is the answer to “what time is it on the clock?”—like 3:15:00—without saying which day it is. The clock goes from the start of the day to a special “end of day” mark Db2 allows. If you also need to know the day, or need split seconds, you want a TIMESTAMP sticker instead.

Exercises

1. State TIME’s range and internal length.
2. Write CREATE TABLE for a class schedule with START_TIME and END_TIME as TIME.
3. Why can’t TIME store milliseconds?
4. Give one scenario for TIME alone and one where TIMESTAMP is clearly better.
5. What can go wrong exchanging TIME with a Java app that only accepts 00:00:00–23:59:59?