---
title: "PostgreSQL DATEDIFF: Days, Hours, Months"
description: "PostgreSQL has no DATEDIFF function. Subtract two dates (end_date - start_date) to get whole days as an integer, subtract two timestamps to get an interval…"
canonical: "https://www.aifordatabase.com/blog/postgresql-datediff/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-09-29
updated: 2026-09-29
---

# PostgreSQL DATEDIFF: Days, Hours, Months

**TL;DR:** PostgreSQL has no DATEDIFF function. Subtract two dates (`end_date - start_date`) to get whole days as an integer, subtract two timestamps to get an interval, and wrap it in `EXTRACT(EPOCH FROM ...)` to get seconds, hours or minutes. For month and year differences use `AGE()` or arithmetic on `EXTRACT(YEAR/MONTH ...)`, depending on whether you want completed units or SQL Server style boundary counts.

The PostgreSQL equivalent of DATEDIFF is plain subtraction. `SELECT date '2001-10-01' - date '2001-09-28';` returns the integer `3`, because in PostgreSQL `date - date` produces the number of days elapsed. For timestamps, `timestamp - timestamp` returns an `interval` such as `63 days 15:00:00`, and `EXTRACT(EPOCH FROM interval)` turns that into seconds you can divide into any unit. The rest of this page gives the exact query for each unit, and explains the one thing most DATEDIFF ports get wrong: SQL Server counts calendar boundaries crossed, while PostgreSQL subtraction measures elapsed time.

## Does PostgreSQL have a DATEDIFF function?

No. DATEDIFF is a SQL Server and MySQL function; PostgreSQL 18 (the current release documented at postgresql.org) ships no function by that name, and calling `DATEDIFF(day, a, b)` fails because PostgreSQL parses `day` as a column name. PostgreSQL instead gives you operators and functions that are more precise:

| You want | SQL Server / MySQL | PostgreSQL |
| --- | --- | --- |
| Days between two dates | `DATEDIFF(day, a, b)` / `DATEDIFF(b, a)` | `b::date - a::date` (integer) |
| Exact elapsed time | no direct equivalent | `b - a` (interval) |
| Seconds | `DATEDIFF(second, a, b)` | `EXTRACT(EPOCH FROM b - a)` |
| Hours | `DATEDIFF(hour, a, b)` | `EXTRACT(EPOCH FROM b - a) / 3600` |
| Minutes | `DATEDIFF(minute, a, b)` | `EXTRACT(EPOCH FROM b - a) / 60` |
| Weeks | `DATEDIFF(week, a, b)` | `(b::date - a::date) / 7` |
| Months | `DATEDIFF(month, a, b)` | `EXTRACT(YEAR FROM AGE(b, a)) * 12 + EXTRACT(MONTH FROM AGE(b, a))` |
| Years | `DATEDIFF(year, a, b)` | `EXTRACT(YEAR FROM AGE(b, a))` |

Note the argument order: MySQL's `DATEDIFF(expr1, expr2)` returns `expr1 - expr2`, SQL Server's `DATEDIFF(datepart, startdate, enddate)` returns end minus start, and in PostgreSQL you simply write `end - start`.

## How do you get the number of days between two dates in PostgreSQL?

Subtracting one `date` from another returns an `integer` count of days. The PostgreSQL manual's own example is `date '2001-10-01' - date '2001-09-28' → 3`.

```
-- Days between two literal dates
SELECT date '2024-09-30' - date '2024-08-24' AS days;   -- 37

-- Days since each customer signed up
SELECT id, current_date - created_at::date AS days_since_signup
FROM customers;
```

If your columns are `timestamp` or `timestamptz`, cast both sides with `::date` first. Casting drops the time of day, so `'2024-08-24 23:59'` and `'2024-08-25 00:01'` are one day apart, which matches SQL Server's `DATEDIFF(day, ...)` behavior. If you want only full 24-hour periods instead, use the interval approach below and take `EXTRACT(DAY FROM b - a)`.

## How do you calculate hours, minutes and seconds between timestamps?

Subtracting two timestamps returns an interval, and `EXTRACT(EPOCH FROM interval)` returns the total number of seconds in it. Divide to get the unit you need.

```
SELECT
  EXTRACT(EPOCH FROM ended_at - started_at)            AS seconds,
  EXTRACT(EPOCH FROM ended_at - started_at) / 60       AS minutes,
  EXTRACT(EPOCH FROM ended_at - started_at) / 3600     AS hours,
  FLOOR(EXTRACT(EPOCH FROM ended_at - started_at) / 3600) AS full_hours
FROM sessions;
```

Two details matter here:

*   **Return type.** Since PostgreSQL 14, `EXTRACT` returns `numeric` instead of `double precision` (float8). The older `DATE_PART('epoch', ...)` still returns `double precision` "for historical reasons", and the manual recommends `EXTRACT` to avoid precision loss. Many tutorials written before 2021 still use `DATE_PART`.
*   **Interval display.** `timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'` returns `63 days 15:00:00`: PostgreSQL folds each 24 hours into a day, similar to `justify_hours()`. The epoch value is still exact.

## How do you get months or years between two dates?

Use `AGE(end, start)`, which returns a symbolic interval in years, months and days. The manual's example: `age(timestamp '2001-04-10', timestamp '1957-06-13')` returns `43 years 9 mons 27 days`.

```
-- Completed years (e.g. customer age, contract tenure)
SELECT EXTRACT(YEAR FROM AGE(current_date, signup_date)) AS years_active
FROM accounts;

-- Completed months
SELECT EXTRACT(YEAR FROM AGE(b, a)) * 12 + EXTRACT(MONTH FROM AGE(b, a)) AS months
FROM (SELECT date '2024-01-15' AS a, date '2024-09-14' AS b) t;  -- 7
```

Partial months are ambiguous because months have different lengths. PostgreSQL uses the month of the earlier date: `age('2004-06-01', '2004-04-30')` yields `1 mon 1 day` (April has 30 days); using May would have given `1 mon 2 days`.

Do not use `EXTRACT(DAY FROM AGE(...))` to get total days. It returns only the days field (27 in the example above), not the total. For total days use date subtraction.

## Why does SQL Server DATEDIFF give different answers than PostgreSQL?

SQL Server's DATEDIFF returns "the count of the specified datepart boundaries crossed", per Microsoft's documentation. It does not measure elapsed time. `DATEDIFF(year, '2024-12-31', '2025-01-01')` returns 1 even though one day passed, and `DATEDIFF(hour, '09:59', '10:01')` returns 1 after two minutes. PostgreSQL's `AGE()` and `EXTRACT(EPOCH ...)` return 0 completed years and 0.03 hours for the same inputs.

When you migrate reports from SQL Server and need identical numbers, reproduce boundary counting explicitly:

```
-- Boundary counts, SQL Server style
SELECT
  b::date - a::date                                                    AS day_boundaries,
  (EXTRACT(YEAR FROM b) - EXTRACT(YEAR FROM a)) * 12
    + (EXTRACT(MONTH FROM b) - EXTRACT(MONTH FROM a))                  AS month_boundaries,
  EXTRACT(YEAR FROM b) - EXTRACT(YEAR FROM a)                          AS year_boundaries,
  EXTRACT(EPOCH FROM date_trunc('hour', b) - date_trunc('hour', a)) / 3600 AS hour_boundaries
FROM (SELECT timestamp '2024-12-31 23:30' AS a, timestamp '2025-01-01 00:10' AS b) t;
-- day 1, month 1, year 1, hour 1
```

A related SQL Server limit does not exist in PostgreSQL: DATEDIFF returns an `int` and raises an error when the result falls outside -2,147,483,648 to 2,147,483,647. For `second` that caps the span at about 68 years; for `millisecond` at about 24 days, which is why Microsoft added `DATEDIFF_BIG`. PostgreSQL's `numeric` EXTRACT has no such overflow.

## What about time zones and daylight saving time?

For `timestamptz` columns, subtraction measures real elapsed time in UTC. Across the US daylight-saving change on 10 March 2024, midnight to midnight in `America/New_York` is `23:00:00`, not `1 day`. If you report "calendar days in the user's zone", convert first and then subtract dates:

```
SELECT (b AT TIME ZONE 'America/New_York')::date
     - (a AT TIME ZONE 'America/New_York')::date AS local_days
FROM events;
```

## Which approach should you use for common reporting questions?

Pick by the question the report is answering:

| Report question | Use |
| --- | --- |
| Days since last login, days to renewal | `current_date - last_login::date` |
| Session or job duration | `EXTRACT(EPOCH FROM ended_at - started_at)` |
| Tenure in completed months (cohorts, churn) | `AGE()` with YEAR\*12 + MONTH |
| Matching a legacy SQL Server report | boundary arithmetic above |
| Bucketing durations into 15-minute bins | `date_bin('15 minutes', ts, origin)` (PostgreSQL 14+) |

For example, a "customers inactive for more than 90 days" list is `WHERE current_date - last_seen_at::date > 90`, and time-to-first-value is `EXTRACT(EPOCH FROM first_query_at - signed_up_at) / 3600` in hours. Our [SaaS churn queries for Postgres](https://www.aifordatabase.com/answers/saas-churn-postgres/) use the same date math on real subscription tables.

If you would rather ask "how many days on average between signup and first purchase?" in plain English and let the SQL be written and run for you, [AI for Database](https://www.aifordatabase.com/databases/postgresql/) connects to PostgreSQL read-only and shows the generated query so you can check the date math. See also [querying PostgreSQL without writing SQL](https://www.aifordatabase.com/blog/query-postgresql-without-sql-2026/) and the [PostgreSQL connection docs](https://www.aifordatabase.com/docs/postgresql/).

Sources: [PostgreSQL date/time functions and operators](https://www.postgresql.org/docs/current/functions-datetime.html), [PostgreSQL 14 release notes](https://www.postgresql.org/docs/14/release-14.html), [Microsoft DATEDIFF (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql).

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### What is the DATEDIFF equivalent in PostgreSQL?

Subtraction. end\_date - start\_date returns an integer number of days for date values, and end\_ts - start\_ts returns an interval for timestamps. Wrap intervals in EXTRACT(EPOCH FROM ...) for seconds.

### How do I calculate the difference in hours in PostgreSQL?

EXTRACT(EPOCH FROM b - a) / 3600. Use FLOOR() around it for completed hours only.

### How do I get the number of months between two dates in PostgreSQL?

EXTRACT(YEAR FROM AGE(b, a)) 12 + EXTRACT(MONTH FROM AGE(b, a)) for completed months, or year and month field arithmetic if you need SQL Server's boundary count.

### Should I use DATE\_PART or EXTRACT?

EXTRACT. Since PostgreSQL 14 it returns numeric; DATE\_PART returns double precision and can lose precision.

### Why is my result one day off compared with SQL Server?

SQL Server counts midnight boundaries crossed, while timestamp subtraction in PostgreSQL counts full 24-hour periods. Cast both values to date before subtracting to match SQL Server.

Read next

*   [HOW TO FIND DUPLICATES IN SQL (AND DELETE THEM SAFELY) →](https://www.aifordatabase.com/blog/find-duplicates-sql-query/)
*   [POSTGRESQL GETDATE() EQUIVALENT: NOW() AND SYSDATE →](https://www.aifordatabase.com/blog/postgresql-getdate/)
*   [SQL PIVOT TABLE: PIVOT, CROSSTAB AND CASE EXAMPLES →](https://www.aifordatabase.com/blog/sql-pivot-table/)

Source: https://www.aifordatabase.com/blog/postgresql-datediff/