MySQL DATEDIFF() to PostgreSQL Date Subtraction Crosswalk

PostgreSQL has no DATEDIFF() function — subtracting two date values directly returns the same whole-day integer that MySQL's DATEDIFF() does.

Close equivalent Covers the common case, but has documented behavioral differences.

MySQL feature: DATEDIFF()
PostgreSQL equivalent: date subtraction (date1 - date2)

Main limitation: PostgreSQL has no DATEDIFF() function; subtracting two `date` values (`date1 - date2`) returns an integer number of days directly, matching MySQL's DATEDIFF(date1, date2) exactly for whole dates.

Quick mapping

MySQL	`DATEDIFF()`
PostgreSQL	`date subtraction (date1 - date2)`

Examples

Difference in whole days between two dates

SELECT DATEDIFF('2026-07-29', '2026-07-01'); -- 28

SELECT DATE '2026-07-29' - DATE '2026-07-01'; -- 28

Both return a plain integer count of days; PostgreSQL uses the subtraction operator directly on `date` values instead of a named function.

Ignoring time-of-day like MySQL does

SELECT DATEDIFF(order_placed_at, order_created_at) FROM orders;

SELECT order_placed_at::date - order_created_at::date FROM orders;

Casting both timestamp columns to `::date` first replicates MySQL's DATEDIFF() truncation of time-of-day before subtracting.

Caveats

Caveat

PostgreSQL has no DATEDIFF() function; subtracting two date values (date1 - date2) returns an integer number of days directly, matching MySQL's DATEDIFF(date1, date2) exactly for whole dates.

Caveat

For timestamp/timestamptz values (which carry a time-of-day), PostgreSQL's subtraction returns an interval, not a plain integer — extract whole days with EXTRACT(DAY FROM (ts1 - ts2)), or cast both sides to ::date first if you want MySQL's whole-day-count behavior regardless of time-of-day.

Caveat

MySQL's DATEDIFF(expr1, expr2) truncates both arguments to their date part before subtracting, ignoring time-of-day entirely; replicate this in PostgreSQL by casting both sides to ::date before subtracting, not by subtracting the raw timestamps.

References

MySQL Reference Manual — DATEDIFF() (v8.4) — checked 2026-07-29
PostgreSQL docs — Date/Time Functions and Operators (v17) — checked 2026-07-29

Last verified 2026-07-29.