MySQL Date Arithmetic

How it works

DATE_ADD and DATE_SUB express interval arithmetic, while ADDDATE and SUBDATE also offer convenient day forms. DATEDIFF compares date portions and returns a day difference, not an exact duration between arbitrary instants. The examples start from a fixed repair day and move by two days. Months and years can involve shorter destination months, so test month-end rules before using the same pattern for billing or renewal policies.

Choose the function from its contract

• DATE_ADD: Adds calendar days.
• ADDDATE: Uses the day-count form.
• DATE_SUB: Subtracts a calendar interval.
• SUBDATE: Uses the day-count form.
• DATEDIFF: Compares date parts rather than exact elapsed hours.

Run in a disposable MySQL lab

Use Oracle MySQL 8.4 with InnoDB, not Bhylu’s SQLite playground. The examples were reviewed on MySQL 8.4.8. XAMPP may provide MariaDB, which has different feature and collation behaviour. Check your actual server before running the script. These lessons do not require a change to Bhylu’s PHP, Apache or normal database service.

1. On a separate lab instance, create the empty bhylu_mysql_lab schema. Stop if it already exists; do not overwrite an existing database.
2. Select that schema and import the complete repair-parts seed once. It sets the documented session mode, utf8mb4 collation, UTC and English date-name locale.
3. Run this lesson in a fresh copy of the seed, not after another lesson’s writes. Use the command-line client or a compatible editor. DELIMITER lines are client instructions for stored-program examples.

Download this complete lesson script. Its setup is the linked seed; review the dataset before changing it. Administrative and destructive examples are restricted to the specifically named disposable objects. Every script repeats the session settings because each new connection has its own session. No MySQL browser runner is currently available.

Example

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION time_zone = '+00:00';
SET SESSION lc_time_names = 'en_US';
SELECT 'DATE_ADD' AS operation, DATE_ADD('2026-02-14',INTERVAL 2 DAY) AS result;
SELECT 'ADDDATE' AS operation, ADDDATE('2026-02-14',2) AS result;
SELECT 'DATE_SUB' AS operation, DATE_SUB('2026-02-14',INTERVAL 2 DAY) AS result;
SELECT 'SUBDATE' AS operation, SUBDATE('2026-02-14',2) AS result;
SELECT 'DATEDIFF' AS operation, DATEDIFF('2026-02-14 10:00:00','2026-02-12 23:00:00') AS result;

Expected result

DATE_ADD | 2026-02-16
ADDDATE | 2026-02-16
DATE_SUB | 2026-02-12
SUBDATE | 2026-02-12
DATEDIFF | 2

The day difference remains two despite the time portions. If the requirement is elapsed seconds, choose an appropriate instant representation and duration operation instead. Around daylight-saving changes, a local calendar day need not be exactly twenty-four hours. This UTC fixture avoids those changes but does not eliminate the need for a real timezone policy.