Dates and times

What replaces DATEDIFF in PostgreSQL?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

PostgreSQL has no DATEDIFF function. Subtract one date from the other: DATE '2026-03-10' - DATE '2026-01-01' returns 68, an integer count of days.

For hours or minutes between two timestamps, subtract them and convert the result with EXTRACT(EPOCH FROM ...). For months or years, use age(). You will find all three below.

Those tables are employees, one row per employee, and job_history, one row per role a person has held with a start_date and an end_date that is null while the role is current. Both are below. The section on hours and minutes needs a pair of timestamps, not dates, so it reads a third table, sessions, one row per analytics session.

You can edit and run three of the queries below in your browser, and there are two exercises at the end. See our other guides to SQL dates in PostgreSQL if you came here with a different date problem.

Schema · hr2 tables? = nullable

employees

idinteger

nametext

emailtext

department_id?integer

manager_id?integer

hire_datedate

titletext

is_activeboolean

job_history

idinteger

employee_id?integer

titletext

department_id?integer

start_datedate

end_date?date

SELECT (SELECT COUNT(*) FROM employees) AS employee_rows,
       (SELECT COUNT(*) FROM job_history) AS job_history_rows,
       (SELECT COUNT(*) FROM job_history WHERE end_date IS NOT NULL) AS roles_that_ended;

employee_rows	job_history_rows	roles_that_ended
60	30	10

the two hr tables; the hours section further down reads a third, sessions

What happens if you run SQL Server’s DATEDIFF in PostgreSQL?

Run it and you get an error about a missing column, which is not what went wrong. Here is the SQL Server habit, against a table of job history:

SELECT DATEDIFF(day, start_date, end_date)
FROM job_history;

PostgreSQL responds column "day" does not exist

PostgreSQL resolves the arguments before it resolves the function, so it reads day as a column name rather than as a unit. It looks for a column named day, finds none, and stops there.

Writing your own datediff() does not rescue the bare-word call, which is worth knowing before you try it. With a real datediff(text, date, date) installed, DATEDIFF(day, ...) still fails with column "day" does not exist, because the bare word never reaches your function. Quote the unit in that same database and the call resolves and runs, which is the proof that the missing function was never the problem. Give the table a column named day instead and PostgreSQL gets one step further, to function datediff(integer, date, date) does not exist. On a stock PostgreSQL, with no function of that name, quoting the unit reaches that function-resolution error directly:

SELECT DATEDIFF('day', start_date, end_date)
FROM job_history;

PostgreSQL responds function datediff(unknown, date, date) does not exist

Run it yourself, then try the replacement in the next section:

SELECT DATEDIFF(day, start_date, end_date)
FROM job_history;

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

Subtract them. Take one DATE from another and you get the number of days between them, as an integer. No function involved. Here is how long each finished role lasted:

SELECT e.name,
       j.title,
       j.start_date,
       j.end_date,
       j.end_date - j.start_date AS days_in_role
FROM job_history j
JOIN employees e ON e.id = j.employee_id
WHERE j.end_date IS NOT NULL
ORDER BY j.id;

name	title	start_date	end_date	days_in_role
Sam Torres	Junior Engineer	2020-03-15	2022-07-01	838
Tara Upton	Junior Engineer	2020-06-01	2022-07-01	760
Val Webb	Junior Engineer	2020-09-01	2022-07-01	668
Wade Xu	Software Engineer	2021-03-01	2023-01-01	671
Zoe Adams	Junior Engineer	2022-01-10	2023-07-01	537
Drew Evans	Associate PM	2020-11-01	2022-07-01	607
Hope Ivers	Sales Rep	2020-08-01	2022-07-01	699
Lane Moore	Sales Rep	2022-01-10	2024-01-01	721

one row per role that has ended · 2 more rows not shown

Sam Torres was a Junior Engineer for 838 days. You get a plain number back, which you can confirm by asking PostgreSQL for its type:

SELECT DATE '2022-07-01' - DATE '2020-03-15' AS days,
       pg_typeof(DATE '2022-07-01' - DATE '2020-03-15') AS type;

days	type
838	integer

Because it is an integer, you can compare it, sum it, or average it like any other number.

Timestamps are a different question, because you can mean two numbers by “days between”. Cast both sides to date and you count the midnights between the two moments: from 09:00 on 1 January to 08:00 on 3 January, ended_at::date - started_at::date gives 2, although only 1 day and 23 hours went by. For the whole days that did go by, divide the subtraction into seconds and round down: FLOOR(EXTRACT(EPOCH FROM ended_at - started_at) / 86400) gives 1 for that same pair. The last section on this page is about the same two numbers.

Try it: replace j.end_date - j.start_date with age(j.end_date, j.start_date) and run it again. Instead of a day count you get years, months and days.

SELECT e.name,
       j.title,
       j.start_date,
       j.end_date,
       j.end_date - j.start_date AS days_in_role
FROM job_history j
JOIN employees e ON e.id = j.employee_id
WHERE j.end_date IS NOT NULL
ORDER BY j.id;

How do you get hours or minutes between two timestamps?

Subtract the timestamps, then convert the result to seconds with EXTRACT(EPOCH FROM ...) and divide. Subtract two timestamps and you get an interval such as 00:42:00, not a number. Here are the first five sessions that have ended:

Schema · analytics1 table? = nullable

sessions

idinteger

user_id?integer

started_attimestamptz

ended_at?timestamptz

event_countinteger

SELECT id,
       ended_at - started_at AS duration,
       ROUND(EXTRACT(EPOCH FROM ended_at - started_at) / 60) AS minutes,
       ROUND(EXTRACT(EPOCH FROM ended_at - started_at) / 3600, 2) AS hours
FROM sessions
WHERE ended_at IS NOT NULL
ORDER BY id
LIMIT 5;

id	duration	minutes	hours
1	00:42:00	42	0.70
2	00:40:00	40	0.67
3	00:28:00	28	0.47
4	01:15:00	75	1.25
5	00:50:00	50	0.83

duration is an interval; minutes and hours are numbers

Use EXTRACT(EPOCH FROM ...) to turn the interval into a number of seconds, then divide by 60 for minutes and 3,600 for hours. That division gives you a numeric, which is what ROUND(..., 2) needs. ROUND gives you the nearest whole unit, so a gap of 59 minutes and 30 seconds reads as 60 minutes. Use FLOOR when you mean units that fully elapsed, and ROUND(..., 2) when you want the decimals.

This one reads sessions, which lives in the analytics schema, not with the hr tables the rest of the page uses. Each editor here loads one schema's tables at a time, so this query gets its own editor instead of the two above. In your own database a table in another schema is reachable either way: qualify it as analytics.sessions, or add the schema to your search_path.

SELECT id,
       ended_at - started_at AS duration,
       ROUND(EXTRACT(EPOCH FROM ended_at - started_at) / 60) AS minutes,
       ROUND(EXTRACT(EPOCH FROM ended_at - started_at) / 3600, 2) AS hours
FROM sessions
WHERE ended_at IS NOT NULL
ORDER BY id
LIMIT 5;

How do you get months or years between two dates?

Use age() to get the gap in years, months and days, counted on the calendar. For a single number of months, multiply the years by 12 and add the months:

SELECT e.name,
       j.start_date,
       j.end_date,
       age(j.end_date, j.start_date) AS time_in_role,
       EXTRACT(YEAR FROM age(j.end_date, j.start_date)) * 12
         + EXTRACT(MONTH FROM age(j.end_date, j.start_date)) AS months_in_role
FROM job_history j
JOIN employees e ON e.id = j.employee_id
WHERE j.end_date IS NOT NULL
ORDER BY j.id;

name	start_date	end_date	time_in_role	months_in_role
Sam Torres	2020-03-15	2022-07-01	2 years 3 mons 17 days	27
Tara Upton	2020-06-01	2022-07-01	2 years 1 mon	25
Val Webb	2020-09-01	2022-07-01	1 year 10 mons	22
Wade Xu	2021-03-01	2023-01-01	1 year 10 mons	22
Zoe Adams	2022-01-10	2023-07-01	1 year 5 mons 22 days	17
Drew Evans	2020-11-01	2022-07-01	1 year 8 mons	20
Hope Ivers	2020-08-01	2022-07-01	1 year 11 mons	23
Lane Moore	2022-01-10	2024-01-01	1 year 11 mons 22 days	23

age() counts calendar months, and the month field of an interval is always a whole number · 2 more rows not shown

Keep both parts. Ask for EXTRACT(MONTH FROM age(...)) on its own and you get only the months part: 3 for Sam Torres, not 27. For whole years, EXTRACT(YEAR FROM age(...)) is enough on its own.

One thing to expect before you check these by hand: the days field of an age() result does not add back to the later date. age() borrows the day count from the month the earlier date sits in, so adding 2 years 3 mons 17 days to 2020-03-15 lands on 2022-07-02, a day past the 2022-07-01 it was measured to. The interval describes the gap in calendar parts; it is not an offset you can add back.

When does DATE_PART give the wrong number of days or hours?

When the one field you ask for is not the whole length of the interval. PostgreSQL does not complain, and you get a plausible-looking number, so nobody catches it in review:

SELECT age(DATE '2026-03-01', DATE '2026-01-01') AS age,
       DATE_PART('day', age(DATE '2026-03-01', DATE '2026-01-01')) AS date_part_day,
       DATE '2026-03-01' - DATE '2026-01-01' AS actual_days;

age	date_part_day	actual_days
2 mons	0	59

59 days apart, and DATE_PART says 0

PostgreSQL describes this gap as 2 mons, and the days field of 2 mons is zero. Try the same thing with hours. Subtract two timestamps and PostgreSQL puts the whole days in one field and the leftover hours in another, so you get back only the hours left over:

SELECT TIMESTAMP '2026-03-10 18:00' - TIMESTAMP '2026-03-08 09:00' AS elapsed,
       DATE_PART('hour', TIMESTAMP '2026-03-10 18:00' - TIMESTAMP '2026-03-08 09:00') AS date_part_hour,
       ROUND(EXTRACT(EPOCH FROM TIMESTAMP '2026-03-10 18:00' - TIMESTAMP '2026-03-08 09:00') / 3600) AS hours;

elapsed	date_part_hour	hours
2 days 09:00:00	9	57

57 hours apart, and DATE_PART says 9

Subtract the earlier timestamp from the later one and DATE_PART('day', ...) does give you the whole days, because PostgreSQL leaves no months in that interval: the days field of 2 days 09:00:00 is 2. The trap is age(), where PostgreSQL states the gap in months and leaves the days field holding the leftovers. Between two dates there is no interval to take a field from at all, so plain subtraction is the answer there. For hours, subtract two timestamps and wrap EXTRACT(EPOCH FROM ...) around the result. You get the whole gap in seconds, so divide by 3,600.

Keep EXTRACT(EPOCH FROM ...) away from age(), though, because that pairing is the same shape of bug. On an age() result PostgreSQL counts every month as 30 days, so EXTRACT(EPOCH FROM age(DATE '2026-03-01', DATE '2026-01-01')) gives you 5184000 seconds, which is 60 days rather than the real 59.

PostgreSQL is louder about the opposite mistake. Subtract two dates and you already have an integer, and an integer has no day field to extract:

SELECT EXTRACT(DAY FROM end_date - start_date)
FROM job_history;

PostgreSQL responds function pg_catalog.extract(unknown, integer) does not exist

Does PostgreSQL count days the way SQL Server’s DATEDIFF does?

On two dates, yes. Microsoft documents SQL Server’s DATEDIFF as counting the date boundaries crossed between the two values, and that is what end_date - start_date gives you as well. A DATE has no time of day, so the boundaries crossed and the days that passed are the same number.

The two engines part company as soon as either value carries a time of day. Subtract two timestamps in PostgreSQL and you measure the time that passed, while SQL Server goes on counting midnights. Look at two hours either side of midnight:

SELECT TIMESTAMP '2026-01-02 01:00' - TIMESTAMP '2026-01-01 23:00' AS elapsed,
       TIMESTAMP '2026-01-02 01:00'::date
         - TIMESTAMP '2026-01-01 23:00'::date AS calendar_days;

elapsed	calendar_days
02:00:00	1

two hours elapsed, one midnight crossed

To count midnights the way DATEDIFF(day, ...) does, cast both values to date before subtracting, as in the calendar_days column. Cast a date column to date and nothing changes, which is why the day counts earlier on this page need none.

PostgreSQL has no DATEADD either, and you hit the same wall for the same reason. See adding days and months to a date.

Practice: the days between two dates

Subtracting dates is part of date arithmetic and intervals of our free SQL course. The lesson explains why a DATE subtracts to an integer and a timestamp subtracts to an interval, and the date and time types cover the difference between the two. No account, nothing to install, nothing to pay.