DATE_ADD / DATEDIFF

-- Adds an interval to a date (MySQL).
SELECT DATE_ADD(date, INTERVAL value unit);

-- Subtracts an interval from a date (MySQL).
SELECT DATE_SUB(date, INTERVAL value unit);

-- Returns the difference in days between two dates (MySQL).
SELECT DATEDIFF(date1, date2);

-- Returns the difference between two dates/datetimes (PostgreSQL).
SELECT AGE(date1, date2);

Syntax	Description
DATE_ADD(date, INTERVAL value unit)	Returns the date after adding the specified interval to the given date (MySQL).
DATE_SUB(date, INTERVAL value unit)	Returns the date after subtracting the specified interval from the given date (MySQL).
INTERVAL value unit	An expression representing a time interval. Supported units include DAY, MONTH, YEAR, HOUR, MINUTE, and SECOND.
DATEDIFF(date1, date2)	Returns the number of days between date1 and date2 (date1 − date2) (MySQL).
AGE(date1, date2)	Returns the difference between two dates in years, months, and days format (PostgreSQL).

-- Get the date 30 days from today.
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY) AS deadline;

-- Get the date 3 months before today.
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH) AS three_months_ago;

-- Calculate the estimated delivery date, 7 days after the order date.
SELECT order_id, order_date,
       DATE_ADD(order_date, INTERVAL 7 DAY) AS estimated_delivery
FROM orders;

-- Get the number of days since each member registered.
SELECT member_id, name,
       DATEDIFF(CURRENT_DATE, registered_at) AS days_since_joined
FROM members;

-- Get orders placed 30 or more days ago.
SELECT order_id, order_date
FROM orders
WHERE order_date <= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

-- Example result of: SELECT member_id, name, DATEDIFF(CURRENT_DATE, registered_at) AS days_since_joined FROM members;
-- +-----------+----------+------------------+
-- | member_id | name     | days_since_joined |
-- +-----------+----------+------------------+
-- |         1 | Tanaka   |              365 |
-- |         2 | Suzuki   |              120 |
-- |         3 | Sato     |               30 |
-- +-----------+----------+------------------+

In PostgreSQL, you can add an interval to a date using the + INTERVAL operator. The difference between two dates is calculated with subtraction.

-- Get the date 30 days from today (PostgreSQL).
SELECT CURRENT_DATE + INTERVAL '30 days' AS deadline;

-- Get the difference in days between two dates (PostgreSQL).
SELECT CURRENT_DATE - registered_at AS days_since_joined FROM members;

-- Get the difference in years, months, and days format (PostgreSQL).
SELECT AGE(CURRENT_DATE, registered_at) FROM members;

In Oracle, you can add days by using + number. To add months, use ADD_MONTHS.

-- Get the date 30 days from today (Oracle).
SELECT SYSDATE + 30 AS deadline FROM DUAL;

-- Get the date 3 months ago (Oracle).
SELECT ADD_MONTHS(SYSDATE, -3) AS three_months_ago FROM DUAL;

In SQL Server, use DATEADD to add an interval and DATEDIFF to find the difference.

-- Get the date 30 days from today (SQL Server).
SELECT DATEADD(DAY, 30, GETDATE()) AS deadline;

-- Get the number of days since each member registered (SQL Server).
SELECT member_id, name,
       DATEDIFF(DAY, registered_at, GETDATE()) AS days_since_joined
FROM members;

In SQLite, use the date function with modifiers to perform date calculations.

-- Get the date 30 days from today (SQLite).
SELECT date('now', '+30 days') AS deadline;

-- Get the date 3 months ago (SQLite).
SELECT date('now', '-3 months') AS three_months_ago;

DATE_ADD and DATE_SUB are MySQL-specific functions, but the standard SQL + INTERVAL syntax (e.g., order_date + INTERVAL '7' DAY) also works in PostgreSQL. If portability matters, prefer the standard SQL syntax.

DATEDIFF(date1, date2) in MySQL returns the number of days as date1 − date2. In PostgreSQL, you can use the subtraction operator (date1 - date2) to get the integer difference in days.

To retrieve the current date or datetime, see CURRENT_DATE / NOW. To extract parts such as year, month, or day from a date, see EXTRACT / FORMAT.