Date arithmetic with intervals in PostgreSQL

Introduction

Date arithmetic helps you add or subtract time from dates to find new dates easily.

Calculate a due date by adding days to a start date.

Find the date 3 months before a given date.

Determine how many days are left until an event.

Adjust timestamps by adding hours or minutes.

Create reports that compare dates with intervals.

Syntax

PostgreSQL

date + interval 'amount unit'
date - interval 'amount unit'

The interval is written as a string with a number and a time unit like '5 days' or '2 months'.

You can add or subtract intervals from dates or timestamps.

Examples

Adds 10 days to June 1, 2024, resulting in June 11, 2024.

PostgreSQL

SELECT DATE '2024-06-01' + INTERVAL '10 days';

Subtracts 1 month from June 1, 2024, resulting in May 1, 2024.

PostgreSQL

SELECT DATE '2024-06-01' - INTERVAL '1 month';

Adds 3 hours to the timestamp, resulting in 3 PM on June 1, 2024.

PostgreSQL

SELECT TIMESTAMP '2024-06-01 12:00' + INTERVAL '3 hours';

Finds the date 7 days from today.

PostgreSQL

SELECT CURRENT_DATE + INTERVAL '7 days';

Sample Program

This query shows adding 5 days, subtracting 2 months, and adding 4 hours to given dates and timestamps.

PostgreSQL

SELECT
  DATE '2024-06-15' + INTERVAL '5 days' AS plus_five_days,
  DATE '2024-06-15' - INTERVAL '2 months' AS minus_two_months,
  TIMESTAMP '2024-06-15 08:00' + INTERVAL '4 hours' AS plus_four_hours;

Important Notes

You can combine multiple intervals by adding them together, like INTERVAL '1 day' + INTERVAL '2 hours'.

Be careful with months and years because they vary in length; PostgreSQL handles this correctly.

Intervals can be negative to subtract time.

Summary

Date arithmetic lets you add or subtract time easily using intervals.

Intervals are written as strings with a number and a unit like 'days', 'months', or 'hours'.

You can use this to calculate new dates or times based on existing ones.