Interval

Introduction to the PostgreSQL INTERVAL data type

The INTERVAL data type stores a duration — a period of time rather than a specific point in time. Intervals are essential for date arithmetic: calculating expiry dates, measuring elapsed time, filtering recent records, and aggregating durations.

The syntax for an interval literal is:

@ interval [ fields ] [ (p) ]

The @ sign is optional. The optional precision p (0–6) controls the number of fractional digits retained in the seconds field. An interval requires 16 bytes and supports a range of approximately -178,000,000 to +178,000,000 years.

Interval input formats

PostgreSQL accepts several syntaxes for interval literals:

-- Verbose (postgres) format
INTERVAL '1 year 2 months 3 days'
INTERVAL '2 weeks ago'
INTERVAL '3 hours 20 minutes'

-- ISO 8601 designator format
INTERVAL 'P6Y5M4DT3H2M1S'

-- ISO 8601 alternative format
INTERVAL 'P0006-05-04T03:02:01'

Interval arithmetic with dates and timestamps

Intervals are most useful when combined with TIMESTAMP or DATE values using arithmetic operators:

-- Timestamp from 1 year and 3 hours 20 minutes ago
SELECT
  now(),
  now() - INTERVAL '1 year 3 hours 20 minutes' AS "3 hrs 20 min ago last year";

-- Output:
-- now                            | 3 hrs 20 min ago last year
-- -------------------------------+-------------------------------------
-- 2024-01-31 21:34:52.242914-05  | 2023-01-31 18:14:52.242914-05

-- Interval arithmetic
SELECT INTERVAL '2h 50m' + INTERVAL '10m';   -- 03:00:00
SELECT INTERVAL '2h 50m' - INTERVAL '50m';   -- 02:00:00
SELECT 600 * INTERVAL '1 minute';            -- 10:00:00

Using INTERVAL in a table

Store event durations in an INTERVAL column and query against them:

CREATE TABLE event (
  id          SERIAL PRIMARY KEY,
  event_name  VARCHAR(255) NOT NULL,
  duration    INTERVAL NOT NULL
);

INSERT INTO event (event_name, duration) VALUES
  ('pgConf', '1 hour 30 minutes'),
  ('pgDAY',  '2 days 5 hours')
RETURNING *;

-- Retrieve events longer than one day
SELECT * FROM event WHERE duration > INTERVAL '1 day';

-- Sum all durations
SELECT SUM(duration) AS total_duration FROM event;

Extracting interval components

-- Extract minutes from an interval
SELECT EXTRACT(MINUTE FROM INTERVAL '5 hours 21 minutes');
-- Returns: 21

-- Extract components from a table column
SELECT
  event_name,
  duration,
  EXTRACT(DAY    FROM duration) AS days,
  EXTRACT(HOUR   FROM duration) AS hours,
  EXTRACT(MINUTE FROM duration) AS minutes
FROM event;

Converting intervals to strings

SELECT TO_CHAR(INTERVAL '17h 20m 05s', 'HH24:MI:SS');
-- Returns: 17:20:05

Adjusting interval values

SELECT justify_days(INTERVAL '30 days');     -- 1 mon
SELECT justify_hours(INTERVAL '24 hours');   -- 1 day
SELECT justify_interval(INTERVAL '1 year -1 hour');
-- Returns: 11 mons 29 days 23:00:00

Production tips

• Use INTERVAL literals instead of integer day counts when doing date arithmetic — it makes intent explicit and handles month/year boundaries correctly.
• Avoid storing intervals as plain integers (e.g. seconds) — you lose the ability to use PostgreSQL's built-in interval functions and comparisons.
• Use justify_interval() before displaying durations to users so "30 days" shows as "1 month" rather than an ambiguous day count.
• When filtering by recency (e.g. WHERE created_at >= now() - INTERVAL '30 days'), verify the query uses an index on the timestamp column with EXPLAIN.

Reference: PostgreSQL documentation — Date/Time types.