PostgreSQL DATEDIFF: Days, Hours, Months

AAI for Database TeamSEP 29 2026 · 6 MIN

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 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 connects to PostgreSQL read-only and shows the generated query so you can check the date math. See also querying PostgreSQL without writing SQL and the PostgreSQL connection docs.

Sources: PostgreSQL date/time functions and operators, PostgreSQL 14 release notes, Microsoft DATEDIFF (Transact-SQL).

FAQ

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.