TIME

Introduction to the PostgreSQL TIME data type

The TIME data type stores a time of day without any date component. It is useful for recording shift schedules, recurring event times, and business hours. The type requires 8 bytes and stores values in the range 00:00:00 to 24:00:00.

Declaration syntax:

column_name TIME(precision);

The optional precision (0–6) specifies the number of fractional digits retained in the seconds field. PostgreSQL accepts most reasonable TIME formats including ISO 8601 and SQL-standard styles:

01:02           -- HH:MI
01:02:03        -- HH:MI:SS
010203          -- HHMISS
04:05:06.777777 -- HH:MI:SS.pppppp

TIME data type example: work shifts

A common use of TIME is modeling shift schedules where start and end times repeat daily:

CREATE TABLE shifts (
  id         SERIAL PRIMARY KEY,
  shift_name VARCHAR NOT NULL,
  start_at   TIME NOT NULL,
  end_at     TIME NOT NULL
);

INSERT INTO shifts (shift_name, start_at, end_at) VALUES
  ('Morning',   '08:00:00', '12:00:00'),
  ('Afternoon', '13:00:00', '17:00:00'),
  ('Night',     '18:00:00', '22:00:00');

SELECT * FROM shifts;

-- Output:
-- id | shift_name | start_at |  end_at
-- ---+------------+----------+----------
--  1 | Morning    | 08:00:00 | 12:00:00
--  2 | Afternoon  | 13:00:00 | 17:00:00
--  3 | Night      | 18:00:00 | 22:00:00

Getting the current time

-- Current time with time zone
SELECT CURRENT_TIME;
-- 00:51:02.746572-08

-- Current time with specific precision
SELECT CURRENT_TIME(5);
-- 00:52:12.19515-08

-- Local time (no time zone offset)
SELECT LOCALTIME;
-- 00:52:40.227186

-- Local time rounded to the second
SELECT LOCALTIME(0);
-- 00:56:08

Converting time to a different time zone

SELECT LOCALTIME AT TIME ZONE 'UTC-7';
-- 16:02:38.902271+07

Extracting components from a TIME value

SELECT
  LOCALTIME,
  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 '10:00' - time '02:00' AS result;
-- 08:00:00

-- Adding an interval to a time returns a TIME
SELECT LOCALTIME + interval '2 hours' AS result;
-- 03:16:18.020418

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 — PostgreSQL recommends using TIMESTAMPTZ instead, because time zones 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.
• Use LOCALTIME(0) when you need the current second-precision time without microseconds, e.g. for logging.

Reference: PostgreSQL documentation — Date/Time types.