How to Calculate Date Difference in PostgreSQL

Galaxy Glossary

How do you calculate the difference between two dates in PostgreSQL?

PostgreSQL lets you subtract two dates or use the age() function to return the exact interval between them.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

How do I get the number of days between two dates?

Subtract one DATE from another. PostgreSQL returns an integer showing the day difference, positive or negative.

SELECT order_date - created_at AS days_to_purchase FROM Orders o JOIN Customers c ON c.id = o.customer_id;

When should I use age() instead of subtraction?

Use age() when you need a full interval (years, months, days).age() normalizes months and years, making it ideal for reporting customer tenure.

SELECT age(current_date, created_at) AS customer_age FROM Customers;

How can I return the difference in hours or minutes?

Cast the dates to TIMESTAMP and subtract; PostgreSQL returns an INTERVAL you can extract parts from.

SELECT EXTRACT(EPOCH FROM (order_date - created_at))/3600 AS hours_gap FROM Orders;

Can I calculate average order cycle time?

Yes.Subtract and average the INTERVAL, then display in days with EXTRACT.

SELECT ROUND(AVG(EXTRACT(EPOCH FROM (shipped_at - order_date))/86400),2) AS avg_cycle_days FROM Orders;

Best practices for date difference calculations

Store dates in UTC, use DATE for day-level math, TIMESTAMP WITH TIME ZONE for sub-day precision, and index date columns for speed.

Common mistakes and fixes

Confusing age() output or mixing TIMESTAMP types causes wrong results. Always inspect the returned data type and cast explicitly.

.

Why How to Calculate Date Difference in PostgreSQL is important

How to Calculate Date Difference in PostgreSQL Example Usage


-- Find customers who purchased within 7 days of signup
SELECT c.id,
       c.name,
       o.id   AS order_id,
       o.order_date,
       o.order_date - c.created_at AS days_to_purchase
FROM   Customers c
JOIN   Orders    o ON o.customer_id = c.id
WHERE  o.order_date - c.created_at <= 7;

How to Calculate Date Difference in PostgreSQL Syntax


-- Basic day difference
SELECT end_date - start_date AS days_diff
FROM   Orders;

-- Detailed interval
SELECT age(end_date, start_date) AS full_interval
FROM   Orders;

-- Hours between order placement and shipment
SELECT EXTRACT(EPOCH FROM (shipped_at - order_date))/3600 AS hours_diff
FROM   Orders;

-- Using a parameterized date
SELECT customer_id,
       order_date,
       order_date - $1::date AS days_since_campaign
FROM   Orders;

Common Mistakes

Using age() for exact day counts. age() normalizes months, so 2024-03-01 to 2024-02-28 returns 2 days, not 1. Fix: use subtraction for pure day counts.
Mixing TIMESTAMP WITHOUT TIME ZONE with TIMESTAMP WITH TIME ZONE. PostgreSQL converts silently, leading to unexpected offsets. Fix: store and compare like-for-like types or cast explicitly.

Frequently Asked Questions (FAQs)

Is date subtraction faster than age()?

Yes. Subtracting two DATE values is a simple integer operation, usually faster than calling age(), which builds an INTERVAL.

Does PostgreSQL account for leap years?

Both subtraction and age() handle leap years automatically. February 29 is treated as an extra day when present.

Can I index an expression like (order_date - created_at)?

Use a generated column storing the difference and index that column, or create a functional index on (order_date - created_at).

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie