How to Use INTERVAL in PostgreSQL
PostgreSQL's INTERVAL type represents a duration of time rather than a specific point in time. While TIMESTAMP tells you 'when,' INTERVAL tells you 'how long.' This distinction makes INTERVAL...
Key Insights
- PostgreSQL’s INTERVAL type stores time durations (not points in time), enabling powerful date arithmetic for scheduling, retention policies, and time-based calculations
- Intervals support multiple input formats including string literals, ISO 8601, and the MAKE_INTERVAL function, with fields ranging from microseconds to years
- Month and year intervals behave differently than day/hour intervals due to variable month lengths—use JUSTIFY functions to normalize and avoid unexpected results in date arithmetic
Understanding the INTERVAL Data Type
PostgreSQL’s INTERVAL type represents a duration of time rather than a specific point in time. While TIMESTAMP tells you “when,” INTERVAL tells you “how long.” This distinction makes INTERVAL essential for calculating time differences, scheduling future events, and implementing time-based business logic.
Unlike simple integer day counts, INTERVAL preserves granularity across multiple time units simultaneously. You can represent “1 year, 2 months, 3 days, 4 hours, and 30 minutes” as a single value, and PostgreSQL handles the complex arithmetic when you add it to timestamps.
Here’s the basic syntax:
SELECT
'1 day'::INTERVAL,
'2 hours 30 minutes'::INTERVAL,
'3 months'::INTERVAL,
'1 year 6 months 15 days'::INTERVAL;
INTERVAL values are stored internally with three components: months, days, and microseconds. This structure allows PostgreSQL to handle the variable lengths of months and the complexities of daylight saving time transitions.
Creating INTERVAL Values
PostgreSQL offers multiple ways to create interval values, each suited to different scenarios.
String Literals
The most readable approach uses PostgreSQL’s interval string format:
SELECT
INTERVAL '5 days',
INTERVAL '2 weeks',
INTERVAL '3 hours 45 minutes',
INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';
You can also use abbreviated units:
SELECT
INTERVAL '5 d',
INTERVAL '3 h 45 m',
INTERVAL '1 y 2 mon';
ISO 8601 Format
For interoperability with other systems, use ISO 8601 duration format:
SELECT
INTERVAL 'P1Y2M3D', -- 1 year, 2 months, 3 days
INTERVAL 'PT4H5M6S', -- 4 hours, 5 minutes, 6 seconds
INTERVAL 'P1Y2M3DT4H5M6S'; -- Combined
MAKE_INTERVAL Function
For programmatic construction, especially when dealing with variables, use MAKE_INTERVAL:
SELECT
MAKE_INTERVAL(years => 1, months => 2, days => 3),
MAKE_INTERVAL(hours => 4, mins => 30, secs => 45.5),
MAKE_INTERVAL(weeks => 2); -- Converted to 14 days
This approach is particularly useful in application code where you’re building intervals dynamically:
-- Calculate retention period from configuration
SELECT MAKE_INTERVAL(days => retention_days)
FROM configuration
WHERE setting_name = 'data_retention';
Arithmetic Operations with INTERVAL
INTERVAL really shines in date arithmetic. You can add and subtract intervals from dates and timestamps, and perform mathematical operations on intervals themselves.
Adding and Subtracting from Dates
SELECT
NOW() + INTERVAL '1 day' AS tomorrow,
NOW() - INTERVAL '1 week' AS last_week,
CURRENT_DATE + INTERVAL '3 months' AS three_months_out,
TIMESTAMP '2024-01-15 10:00:00' + INTERVAL '2 hours 30 minutes' AS later;
Calculating Differences
Subtracting timestamps or dates produces an interval:
SELECT
TIMESTAMP '2024-12-31 23:59:59' - TIMESTAMP '2024-01-01 00:00:00' AS year_duration,
CURRENT_DATE - DATE '2024-01-01' AS days_since_new_year;
Interval Arithmetic
You can multiply, divide, and combine intervals:
SELECT
INTERVAL '1 day' * 7 AS one_week,
INTERVAL '1 hour' * 2.5 AS two_and_half_hours,
INTERVAL '10 days' / 2 AS five_days,
INTERVAL '1 day' + INTERVAL '12 hours' AS thirty_six_hours;
This is useful for scaling time periods:
-- Calculate total project time from daily estimates
SELECT
task_name,
estimated_days,
INTERVAL '8 hours' * estimated_days AS total_hours
FROM project_tasks;
Essential INTERVAL Functions
PostgreSQL provides several functions for working with intervals effectively.
AGE Function
AGE calculates the interval between two timestamps, returning a normalized result:
SELECT
AGE(TIMESTAMP '2024-12-31', TIMESTAMP '2020-01-01') AS duration,
AGE(TIMESTAMP '2024-03-15 14:30:00', TIMESTAMP '2024-03-15 09:15:00') AS time_diff;
When called with a single argument, AGE calculates the interval from that timestamp to the current date:
SELECT
user_id,
created_at,
AGE(created_at) AS account_age
FROM users;
EXTRACT Function
Extract specific components from intervals:
SELECT
EXTRACT(DAYS FROM INTERVAL '5 days 3 hours') AS days,
EXTRACT(HOURS FROM INTERVAL '5 days 3 hours') AS hours,
EXTRACT(EPOCH FROM INTERVAL '1 day') AS seconds; -- 86400
Justify Functions
These functions normalize intervals by converting between units:
SELECT
JUSTIFY_DAYS(INTERVAL '35 days') AS normalized_days, -- 1 mon 5 days
JUSTIFY_HOURS(INTERVAL '27 hours') AS normalized_hours, -- 1 day 3 hours
JUSTIFY_INTERVAL(INTERVAL '35 days 27 hours') AS fully_normalized; -- 1 mon 6 days 3 hours
Justification is crucial when you need consistent interval representations for comparison or display.
Practical Use Cases
Data Retention Policies
Delete old records based on retention periods:
-- Delete logs older than 90 days
DELETE FROM application_logs
WHERE created_at < NOW() - INTERVAL '90 days';
-- Archive completed orders after 1 year
INSERT INTO archived_orders
SELECT * FROM orders
WHERE status = 'completed'
AND completed_at < CURRENT_DATE - INTERVAL '1 year';
Scheduling and Reminders
Find upcoming events or overdue tasks:
-- Find appointments in the next 24 hours
SELECT * FROM appointments
WHERE scheduled_time BETWEEN NOW() AND NOW() + INTERVAL '24 hours';
-- Identify overdue invoices (30 day payment terms)
SELECT
invoice_id,
invoice_date,
AGE(CURRENT_DATE, invoice_date) AS days_overdue
FROM invoices
WHERE status = 'unpaid'
AND invoice_date < CURRENT_DATE - INTERVAL '30 days';
SLA Monitoring
Calculate response times and check SLA compliance:
-- Calculate ticket response times
SELECT
ticket_id,
created_at,
first_response_at,
first_response_at - created_at AS response_time,
CASE
WHEN first_response_at - created_at <= INTERVAL '1 hour'
THEN 'Met SLA'
ELSE 'Missed SLA'
END AS sla_status
FROM support_tickets
WHERE first_response_at IS NOT NULL;
Time-Series Data Generation
Generate sequences of timestamps for reporting:
-- Generate daily timestamps for the last 30 days
SELECT
generate_series(
CURRENT_DATE - INTERVAL '30 days',
CURRENT_DATE,
INTERVAL '1 day'
)::DATE AS report_date;
-- Create hourly buckets for today
SELECT
generate_series(
CURRENT_DATE,
CURRENT_DATE + INTERVAL '23 hours',
INTERVAL '1 hour'
) AS hour_bucket;
Pitfalls and Best Practices
Month and Year Ambiguity
Adding months to dates can produce unexpected results due to variable month lengths:
SELECT
DATE '2024-01-31' + INTERVAL '1 month' AS result; -- 2024-02-29 (leap year)
SELECT
DATE '2024-03-31' + INTERVAL '1 month' AS result; -- 2024-04-30 (April has 30 days)
PostgreSQL adjusts to the last valid day of the month. If you need precise day-of-month handling, work with day intervals instead:
-- More predictable for fixed day counts
SELECT DATE '2024-01-31' + INTERVAL '30 days'; -- 2024-03-01
Timezone Considerations
INTERVAL operations respect the timezone of timestamp values:
-- Different results due to DST transitions
SELECT
TIMESTAMP WITH TIME ZONE '2024-03-10 00:00:00 America/New_York' + INTERVAL '1 day' AS with_dst,
TIMESTAMP '2024-03-10 00:00:00' + INTERVAL '1 day' AS without_tz;
Always use TIMESTAMP WITH TIME ZONE for business logic involving intervals across DST boundaries.
Indexing for Performance
When filtering by intervals, ensure your queries can use indexes:
-- Index-friendly (uses index on created_at)
CREATE INDEX idx_logs_created ON application_logs(created_at);
SELECT * FROM application_logs
WHERE created_at > NOW() - INTERVAL '7 days';
-- NOT index-friendly (function on indexed column)
SELECT * FROM application_logs
WHERE NOW() - created_at < INTERVAL '7 days'; -- Avoid this pattern
Always put the interval arithmetic on the constant side of the comparison, not on the column side.
Precision Matters
INTERVAL stores microsecond precision. For performance-critical comparisons, consider if you need that precision:
-- If you only need day precision, truncate timestamps
SELECT * FROM events
WHERE DATE_TRUNC('day', event_time) = CURRENT_DATE - INTERVAL '1 day';
INTERVAL is one of PostgreSQL’s most powerful features for time-based logic. Master it, and you’ll write cleaner, more maintainable code for scheduling, reporting, and data lifecycle management.