Subtract date

2 snippets in SQL

Also written as subtract dates

SQLSQL

Date Addition / Subtraction

syntax

-- PostgreSQL
date + INTERVAL 'n unit'
-- MySQL
DATE_ADD(date, INTERVAL n unit)
-- ANSI
date + INTERVAL 'n' unit

example

-- PostgreSQL
SELECT
  order_date,
  order_date + INTERVAL '30 days' AS due_date,
  order_date - INTERVAL '1 year' AS year_ago
FROM orders;

-- MySQL
SELECT
  order_date,
  DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date
FROM orders;

output

-- order_date  | due_date    | year_ago
-- 2025-10-15  | 2025-11-14  | 2024-10-15

Note Interval units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Adding months is tricky — Jan 31 + 1 month may yield Feb 28 or Mar 3 depending on the database. PostgreSQL truncates to end of month; MySQL may overflow.

Date Difference

SQL · Date Functions

syntax

-- PostgreSQL
date1 - date2  -- returns integer (days)
-- MySQL
DATEDIFF(date1, date2)  -- returns integer (days)
-- Standard: use EXTRACT on interval

example

-- PostgreSQL
SELECT
  order_date,
  shipped_date,
  shipped_date - order_date AS days_to_ship
FROM orders
WHERE shipped_date IS NOT NULL;

-- MySQL
SELECT
  order_date,
  shipped_date,
  DATEDIFF(shipped_date, order_date) AS days_to_ship
FROM orders;

output

-- order_date  | shipped_date | days_to_ship
-- 2025-10-01  | 2025-10-04   | 3

Note PostgreSQL subtracts dates directly. MySQL uses DATEDIFF(end, start) — note the argument order is end first. SQL Server's DATEDIFF takes a unit argument: DATEDIFF(DAY, start, end). Always check argument order in your database.