TIME Data Type in PostgreSQL

The PostgreSQL TIME data type stores a time of day without any date component. It is useful for recording shift schedules, recurring event times, operating hours, and any time-of-day rule that repeats independently of the calendar date.

Syntax

column_name TIME
column_name TIME(precision)           -- optional: 0–6 fractional seconds digits
column_name TIME WITH TIME ZONE       -- stores UTC offset alongside the time
column_name TIMETZ                    -- alias for TIME WITH TIME ZONE

PostgreSQL accepts many TIME input formats:

'08:30:00'       -- HH:MI:SS
'08:30'          -- HH:MI (seconds default to 0)
'083000'         -- HHMISS
'08:30:00.500'   -- with fractional seconds

Practical Example

Model warehouse shift schedules using TIME:

CREATE TABLE shifts (
  id          SERIAL PRIMARY KEY,
  shift_name  VARCHAR(50) NOT NULL,
  starts_at   TIME        NOT NULL,
  ends_at     TIME        NOT NULL,
  CONSTRAINT chk_shift_order CHECK (ends_at > starts_at)
);

INSERT INTO shifts (shift_name, starts_at, ends_at)
VALUES
  ('Morning',   '06:00:00', '14:00:00'),
  ('Afternoon', '14:00:00', '22:00:00'),
  ('Night',     '22:00:00', '23:59:59');

SELECT * FROM shifts ORDER BY starts_at;

 id | shift_name | starts_at |  ends_at
----+------------+-----------+-----------
  1 | Morning    | 06:00:00  | 14:00:00
  2 | Afternoon  | 14:00:00  | 22:00:00
  3 | Night      | 22:00:00  | 23:59:59

Find which shift is active at a given time of day:

SELECT shift_name
FROM shifts
WHERE LOCALTIME BETWEEN starts_at AND ends_at;

Getting the Current Time

SELECT CURRENT_TIME;       -- current time with timezone offset
SELECT CURRENT_TIME(3);    -- rounded to milliseconds
SELECT LOCALTIME;          -- current local time, no offset
SELECT LOCALTIME(0);       -- rounded to the second

Timezone Conversion

SELECT LOCALTIME AT TIME ZONE 'UTC';

Extracting Time Components

SELECT
  LOCALTIME                              AS current_time,
  EXTRACT(HOUR   FROM LOCALTIME)         AS hour,
  EXTRACT(MINUTE FROM LOCALTIME)         AS minute,
  EXTRACT(SECOND FROM LOCALTIME)         AS second,
  EXTRACT(MILLISECONDS FROM LOCALTIME)   AS milliseconds;

Arithmetic with TIME Values

-- Difference between two times returns an INTERVAL
SELECT TIME '14:00:00' - TIME '06:00:00' AS shift_duration;
-- 08:00:00

-- Add an interval to a time
SELECT TIME '06:00:00' + INTERVAL '4 hours 30 minutes' AS break_end;
-- 10:30:00

Testing with Vela

Shift schedule rules often have edge cases around midnight crossings, daylight saving transitions, and boundary conditions (a shift ending at exactly 22:00:00 and the next starting at 22:00:00). Before deploying scheduling logic to production, test the query patterns on a database branch with sample shifts at boundary times to confirm the BETWEEN clauses and arithmetic produce correct results.

Production Tips

• Use TIME only when a time of day truly has no date context — for most application events, TIMESTAMPTZ is safer and more complete.
• Avoid TIME WITH TIME ZONE (TIMETZ) — PostgreSQL recommends using TIMESTAMPTZ instead, because timezone offsets are ambiguous without a date.
• When storing shift or schedule data, consider whether daylight saving time transitions affect your use case — if so, model shifts with full TIMESTAMPTZ values and a recurrence rule.
• Use LOCALTIME(0) when you need the current second-precision time without microseconds, such as for logging or shift lookup.
• Validate TIME column input with CHECK constraints — for example, CHECK (ends_at > starts_at) — to prevent logically invalid schedule rows.