Interval type for durations in PostgreSQL

Introduction

The interval type helps you store and work with durations of time, like hours, days, or months. It makes it easy to add or subtract time periods in your data.

You want to record how long a task took, like 3 hours and 15 minutes.

You need to calculate the difference between two dates or times.

You want to add a duration to a date, like adding 2 weeks to a start date.

You want to store recurring time periods, like a subscription length of 1 month.

You want to compare durations, like checking if one event lasted longer than another.

Syntax

PostgreSQL

INTERVAL '[quantity] [unit]'

The quantity is a number (can be decimal).

The unit can be seconds, minutes, hours, days, weeks, months, years, etc.

Examples

This represents a duration of 3 hours and 15 minutes.

PostgreSQL

INTERVAL '3 hours 15 minutes'

This represents a duration of 1 day.

PostgreSQL

INTERVAL '1 day'

This represents a duration of 2 months and 10 days.

PostgreSQL

INTERVAL '2 months 10 days'

This represents a duration of 45 seconds.

PostgreSQL

INTERVAL '45 seconds'

Sample Program

This query shows today's date, adds 1 month and 5 days to it, and also shows a duration of 2 hours and 30 minutes.

PostgreSQL

SELECT CURRENT_DATE AS today,
       (CURRENT_DATE + INTERVAL '1 month 5 days')::date AS future_date,
       INTERVAL '2 hours 30 minutes' AS duration;

Important Notes

You can add or subtract intervals from dates or timestamps easily.

Intervals can be combined by adding them together.

Be careful with months and years because their length can vary (months can have 28 to 31 days).

Summary

The interval type stores durations like hours, days, or months.

You can use intervals to add or subtract time from dates.

Intervals make working with time periods simple and clear.