The Difference Between Dates in SQL: DATEDIFF and Its Equivalents

Counting how many days passed between two dates is possible in every SQL database — but each DBMS does it its own way. MySQL and SQL Server have a DATEDIFF function, but with different arguments; PostgreSQL doesn't have it at all — there you simply subtract dates. The overview:

Let's go through each DBMS and then solve real tasks: from "how long did the customer think before the first order" to age from a birth date.

DATEDIFF in MySQL

The MySQL version of the function takes two dates and returns the difference in days. The later date goes first:

MySQL 8.1
SELECT DATEDIFF('2026-06-12', '2026-06-01') AS days;

Swap the arguments and the result turns negative — a frequent source of bugs in reports:

MySQL 8.1
SELECT DATEDIFF('2026-06-01', '2026-06-12') AS days;
-- -11

MySQL's DATEDIFF cannot count hours, months or years — that is what TIMESTAMPDIFF is for, and its argument order is the opposite: start first, then end:

MySQL 8.1
SELECT TIMESTAMPDIFF(HOUR, '2026-06-11 10:00:00', '2026-06-12 18:30:00') AS hours;
-- 32: TIMESTAMPDIFF returns whole hours only (32.5 is truncated)

SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-06-12') AS months;
-- 4: complete months between the dates

What does SELECT DATEDIFF('2026-06-01', '2026-06-12') return in MySQL?

DATEDIFF in SQL Server

In SQL Server the function takes three arguments: the unit, the start, the end. Note that the dates come in the reverse order compared to MySQL:

MySQL 8.1
SELECT DATEDIFF(day, '2026-06-01', '2026-06-12') AS days;
-- 11

SELECT DATEDIFF(hour, '2026-06-11 10:00:00', '2026-06-12 18:30:00') AS hours;
-- 32

SQL Server has a treacherous quirk: it counts not complete periods but the number of unit boundaries crossed. The difference "in years" between December 31 and January 1 is one year:

MySQL 8.1
SELECT DATEDIFF(year, '2025-12-31', '2026-01-01') AS years;
-- 1, even though only one day has passed

That is why DATEDIFF(year, ...) cannot be used directly for age or seniority in SQL Server — the result is inflated by almost a year for everyone whose birthday is still ahead.

PostgreSQL: Subtraction Instead of DATEDIFF

A query with DATEDIFF in PostgreSQL ends with an error:

MySQL 8.1
SELECT DATEDIFF('2026-06-12', '2026-06-01');

Instead of a function there is arithmetic: the difference of two DATE values is an integer number of days:

PostgreSQL 17.5
SELECT DATE '2026-06-12' - DATE '2026-06-01' AS days;

With the TIMESTAMP type subtraction returns not a number but an interval:

PostgreSQL 17.5
SELECT TIMESTAMP '2026-06-12 18:30:00' - TIMESTAMP '2026-06-11 10:00:00' AS diff;
-- 1 day 08:30:00

There are two ways to turn an interval into a number:

• EXTRACT(EPOCH FROM ...) — converts the interval to seconds, then it's plain arithmetic:

  PostgreSQL 17.5
  SELECT EXTRACT(EPOCH FROM (TIMESTAMP '2026-06-12 18:30:00' - TIMESTAMP '2026-06-11 10:00:00')) / 3600 AS hours;
  -- 32.5: unlike TIMESTAMPDIFF, the fractional part survives
  

• AGE(...) — returns a "human" interval in years, months and days:

  PostgreSQL 17.5
  SELECT AGE(TIMESTAMP '2026-06-12', TIMESTAMP '1995-08-20') AS age;
  -- 30 years 9 mons 23 days
  

Why does SELECT DATEDIFF('2026-06-12', '2026-06-01') fail in PostgreSQL?

A Live Example: From Registration to the First Order

Using the delivery service database, let's compute how many days pass between a user's registration and their first order — a classic product metric:

The MySQL version:

MySQL 8.1
SELECT
    u.user_id,
    DATE(u.registration_date) AS registered,
    DATE(MIN(o.order_date)) AS first_order,
    DATEDIFF(MIN(o.order_date), u.registration_date) AS days_to_order
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.registration_date
ORDER BY u.user_id
LIMIT 5;

The same query for PostgreSQL:

PostgreSQL 17.5
SELECT
    u.user_id,
    DATE(u.registration_date) AS registered,
    DATE(MIN(o.order_date)) AS first_order,
    MIN(o.order_date)::date - u.registration_date::date AS days_to_order
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.registration_date
ORDER BY u.user_id
LIMIT 5;

The queries differ by a single line — the way the dates are subtracted — and the result is identical.

Age from a Birth Date

Another standard task that is easy to get wrong. The correct variants:

MySQL 8.1
-- MySQL: TIMESTAMPDIFF counts complete years
SELECT TIMESTAMPDIFF(YEAR, '1995-08-20', CURDATE()) AS age;

PostgreSQL 17.5
-- PostgreSQL: AGE returns an interval, extract the years from it
SELECT date_part('year', AGE(DATE '1995-08-20')) AS age;

Both return 30 for someone born on August 20, 1995 (as of June 12, 2026): the birthday hasn't come yet, so 30 complete years. The naive (current year - birth year) would give 31 — one too many until the birthday. Both blocks are runnable — plug in your own birth date and check.

Cheat Sheet: Task → Function

And the main distinction to keep in mind: MySQL's TIMESTAMPDIFF and PostgreSQL's AGE count complete periods, while SQL Server's DATEDIFF counts boundary crossings.

What's Next

• how the DATE, TIME and TIMESTAMP types work — in the lesson Date and time in SQL;
• date functions with exercises — in the lesson Working with date and time;
• quick references — in the handbook: DATEDIFF, TIMESTAMPDIFF;
• practice on real tasks — in the SQL trainer.