INTERVAL Data Type in PostgreSQL

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.

Syntax

column_name INTERVAL
column_name INTERVAL(precision)    -- limit fractional seconds (0–6)

PostgreSQL accepts several syntaxes for interval literals:

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

-- ISO 8601 designator format
INTERVAL 'P1Y2M3DT4H5M6S'

-- ISO 8601 alternative format
INTERVAL 'P0001-02-03T04:05:06'

Practical Example

Track subscription durations in an orders table:

CREATE TABLE subscriptions (
  id                SERIAL PRIMARY KEY,
  product_sku       VARCHAR(50)  NOT NULL,
  started_at        TIMESTAMPTZ  NOT NULL DEFAULT now(),
  billing_interval  INTERVAL     NOT NULL,
  expires_at        TIMESTAMPTZ  GENERATED ALWAYS AS
                    (started_at + billing_interval) STORED
);

INSERT INTO subscriptions (product_sku, billing_interval)
VALUES
  ('PLAN-MONTHLY', '1 month'),
  ('PLAN-ANNUAL',  '1 year'),
  ('PLAN-TRIAL',   '14 days');

SELECT
  product_sku,
  started_at::date   AS start_date,
  billing_interval,
  expires_at::date   AS expiry_date
FROM subscriptions;

Interval Arithmetic with Dates and Timestamps

Intervals are most useful combined with TIMESTAMP or DATE values:

-- Orders placed in the last 7 days
SELECT id, product_sku, placed_at
FROM orders
WHERE placed_at >= now() - INTERVAL '7 days';

-- Add 30 days to find the ship-by date
SELECT
  id,
  order_date,
  order_date + INTERVAL '30 days' AS ship_by_date
FROM orders;

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

Extracting Interval Components

-- Extract hours and minutes from a stored interval
SELECT
  product_sku,
  billing_interval,
  EXTRACT(DAY    FROM billing_interval) AS days,
  EXTRACT(HOUR   FROM billing_interval) AS hours,
  EXTRACT(MINUTE FROM billing_interval) AS minutes
FROM subscriptions;

Normalizing and Formatting Intervals

-- Convert 30 days to "1 month"
SELECT justify_days(INTERVAL '30 days');     -- 1 mon

-- Convert 24 hours to "1 day"
SELECT justify_hours(INTERVAL '24 hours');   -- 1 day

-- Apply both normalizations
SELECT justify_interval(INTERVAL '1 year -1 hour');
-- 11 mons 29 days 23:00:00

-- Format as HH:MM:SS string
SELECT TO_CHAR(INTERVAL '2 hours 15 minutes 30 seconds', 'HH24:MI:SS');
-- 02:15:30

Controlling Interval Display

SET intervalstyle = 'iso_8601';
SELECT INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';
-- P6Y5M4DT3H2M1S

SET intervalstyle = 'postgres';  -- default
SELECT INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';
-- 6 years 5 mons 4 days 03:02:01

Testing with Vela

Interval-based expiry logic often breaks on edge cases: end-of-month boundaries ('1 month' from January 31 lands on March 2 or March 3), daylight saving transitions, and negative durations. Before deploying subscription billing logic to production, test on a database branch with a variety of boundary dates to catch month/year-end edge cases before they appear in billing.

Production Tips

• Use INTERVAL literals instead of integer day counts for date arithmetic — they handle month and year boundaries correctly where plain integers do not.
• Avoid storing durations as plain integers (e.g. seconds) — you lose 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 (WHERE placed_at >= now() - INTERVAL '30 days'), verify the query uses an index on the timestamp column with EXPLAIN ANALYZE.
• Store billing intervals as INTERVAL columns rather than hardcoding them in application logic — this allows per-subscription billing cycles and database-level consistency.