Date Arithmetic and Difference

Learning Focus

Use this lesson to understand date arithmetic — how to add/subtract intervals and calculate differences between dates. These operations power billing cycles, SLA tracking, retention policies, and every time-aware feature in your application.

Concept Overview

What Is Date Arithmetic?

Date arithmetic means performing calculations on date and time values:

Adding time: "What date is 30 days from now?"
Subtracting time: "What was the date 7 days ago?"
Measuring differences: "How many hours between order and delivery?"

Key Functions

Function	Purpose	Returns
`DATE_ADD(date, INTERVAL n unit)`	Add an interval to a date	`DATE` or `DATETIME`
`DATE_SUB(date, INTERVAL n unit)`	Subtract an interval from a date	`DATE` or `DATETIME`
`DATEDIFF(date1, date2)`	Difference in days	`INT`
`TIMESTAMPDIFF(unit, start, end)`	Difference in any unit	`INT`

Available Interval Units

Unit	Example
`SECOND`	`INTERVAL 30 SECOND`
`MINUTE`	`INTERVAL 15 MINUTE`
`HOUR`	`INTERVAL 2 HOUR`
`DAY`	`INTERVAL 7 DAY`
`WEEK`	`INTERVAL 1 WEEK`
`MONTH`	`INTERVAL 3 MONTH`
`QUARTER`	`INTERVAL 1 QUARTER`
`YEAR`	`INTERVAL 1 YEAR`

Basic Syntax & Rules

Adding and Subtracting Intervals

-- Add 30 days
SELECT DATE_ADD('2026-02-10', INTERVAL 30 DAY) AS future_date;
-- Result: 2026-03-12

-- Subtract 7 days
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY) AS week_ago;

-- You can also use + and - operators
SELECT '2026-02-10' + INTERVAL 1 MONTH AS next_month;
-- Result: 2026-03-10

SELECT NOW() - INTERVAL 24 HOUR AS yesterday;

Measuring Differences

-- DATEDIFF: always returns days
SELECT DATEDIFF('2026-12-31', '2026-01-01') AS days_in_year;
-- Result: 364

-- TIMESTAMPDIFF: returns difference in your chosen unit
SELECT TIMESTAMPDIFF(HOUR, '2026-02-10 08:00:00', '2026-02-11 14:30:00') AS hours_diff;
-- Result: 30

SELECT TIMESTAMPDIFF(MONTH, '2025-06-15', '2026-02-10') AS months_diff;
-- Result: 7

Important: DATEDIFF vs. TIMESTAMPDIFF

Feature	`DATEDIFF()`	`TIMESTAMPDIFF()`
Precision	Days only	Any unit (seconds to years)
Argument order	`(end, start)`	`(unit, start, end)` — reversed!
Use case	Simple date comparisons	Precise time measurements

Watch out: DATEDIFF(end, start) but TIMESTAMPDIFF(unit, start, end) — the argument order is different.

Step-by-Step Examples

Example 1: Subscription Renewal Dates

Scenario: Calculate when each customer's subscription renews.

SELECT
    customer_id,
    plan_type,
    start_date,
    CASE plan_type
        WHEN 'monthly'  THEN DATE_ADD(start_date, INTERVAL 1 MONTH)
        WHEN 'quarterly' THEN DATE_ADD(start_date, INTERVAL 3 MONTH)
        WHEN 'annual'    THEN DATE_ADD(start_date, INTERVAL 1 YEAR)
    END AS renewal_date
FROM subscriptions
WHERE status = 'active';

customer_id	plan_type	start_date	renewal_date
101	monthly	2026-01-15	2026-02-15
102	quarterly	2026-01-01	2026-04-01
103	annual	2025-06-10	2026-06-10

Example 2: Delivery Time Tracking

Scenario: Calculate how long each order took to deliver.

SELECT
    order_id,
    created_at,
    delivered_at,
    TIMESTAMPDIFF(HOUR, created_at, delivered_at) AS delivery_hours,
    TIMESTAMPDIFF(MINUTE, created_at, delivered_at) AS delivery_minutes
FROM shipments
WHERE delivered_at IS NOT NULL
ORDER BY delivery_hours DESC
LIMIT 10;

order_id	created_at	delivered_at	delivery_hours	delivery_minutes
9001	2026-02-08 10:00:00	2026-02-09 04:00:00	18	1080
9002	2026-02-07 09:00:00	2026-02-08 12:30:00	27	1650

Example 3: SLA Compliance Check

Scenario: Support tickets must be resolved within 24 hours. Find overdue tickets.

SELECT
    ticket_id,
    created_at,
    TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_open,
    CASE
        WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) > 24
        THEN 'SLA BREACHED'
        ELSE 'Within SLA'
    END AS sla_status
FROM support_tickets
WHERE resolved_at IS NULL
ORDER BY created_at ASC;

ticket_id	created_at	hours_open	sla_status
501	2026-02-08 10:00:00	52	SLA BREACHED
502	2026-02-10 08:00:00	6	Within SLA

Example 4: Data Retention — Purge Old Records

Scenario: Delete audit logs older than 90 days.

-- First, check what would be deleted
SELECT COUNT(*) AS rows_to_delete
FROM audit_logs
WHERE created_at < NOW() - INTERVAL 90 DAY;

-- Then delete
DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL 90 DAY
LIMIT 10000;  -- Batch to avoid long locks

Example 5: Age Calculation

SELECT
    user_id,
    full_name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users
WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;

user_id	full_name	birth_date	age
1	Alice Rahman	1998-05-20	27
2	Bob Santoso	2000-11-03	25

Practical Use Cases

1. Billing and Invoicing

Calculate due dates: invoice_date + INTERVAL 30 DAY. Flag overdue invoices where due_date < CURDATE().

2. SLA Monitoring

Track response times and flag breaches: TIMESTAMPDIFF(HOUR, opened_at, NOW()) > sla_hours.

3. Retention Policies

Purge old data: DELETE WHERE created_at < NOW() - INTERVAL 1 YEAR. Batch deletes to avoid locking.

4. Subscription Management

Calculate renewal dates, trial expiry, and grace periods using DATE_ADD().

5. Analytics and Reporting

"Orders in the last 30 days", "revenue this quarter", "month-over-month growth" — all require date arithmetic.

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Comparing datetimes from mixed timezones	Wrong interval results (e.g., 7 hours off)	Normalize to UTC before computing differences
Using `DATEDIFF()` when you need hours	Only returns whole days, even for datetime inputs	Use `TIMESTAMPDIFF(HOUR, ...)` for hour-level precision
Not handling `NULL` end dates	`TIMESTAMPDIFF()` returns `NULL` if either arg is NULL	Add `WHERE end_date IS NOT NULL` or use `COALESCE()`
Wrong argument order	`DATEDIFF(start, end)` gives a negative number	Remember: `DATEDIFF(end, start)` and `TIMESTAMPDIFF(unit, start, end)`
Month arithmetic surprises	`DATE_ADD('2026-01-31', INTERVAL 1 MONTH)` = `2026-02-28`	Be aware of month-end behavior and test edge cases
Unbounded DELETE for retention	Locks the table for minutes/hours	Use `LIMIT` to batch: `DELETE ... LIMIT 10000` per run

Best Practices

Use TIMESTAMPDIFF() for precision — it handles any unit and gives accurate results
Use DATEDIFF() for simple day counts — it's cleaner when you only need days
Handle NULLs explicitly — delivery dates, resolution times often have NULL end values
Batch deletion for retention — never DELETE FROM ... WHERE created_at < X without a LIMIT
Test month-end edge cases — January 31 + 1 month = February 28 (or 29 in leap years)
Use operator syntax for readability — NOW() - INTERVAL 7 DAY reads better than DATE_SUB(NOW(), INTERVAL 7 DAY)

Hands-On Practice

Exercise 1: Renewal Date Calculator (Easy)

Write a query that shows each subscription's start_date and renewal_date based on plan_type ('monthly', 'quarterly', 'annual').

Exercise 2: SLA Report (Medium)

Create a query that:

Lists all open support tickets
Shows hours since creation
Marks tickets as "SLA BREACHED" if open for more than 24 hours
Orders by longest-open first

Exercise 3: Retention Cleanup Script (Advanced)

Write a batch deletion query that:

Deletes audit log rows older than 90 days
Processes no more than 10,000 rows at a time
Shows how many rows were deleted
Explain how you would schedule this to run daily

Connection to Other Concepts

Related Concept	How It Connects
Current Date/Time Functions	`NOW()` is the starting point for most arithmetic
Formatting	Format the result: `DATE_FORMAT(NOW() + INTERVAL 30 DAY, '%M %d, %Y')`
Event Scheduler	Recurring events use interval expressions for scheduling
Indexes	Date range queries (`BETWEEN`, `>=` / `<`) benefit from indexes on date columns
Retention Policies	`NOW() - INTERVAL N DAY` is the foundation of every data retention query

What to Learn Next

12. Views — create reusable query patterns for your time-based reports

Visual Learning Diagram

flowchart LR
    A["Known Date\n2026-02-10"] -->|"+ INTERVAL 30 DAY"| B["Future Date\n2026-03-12"]
    A -->|"- INTERVAL 7 DAY"| C["Past Date\n2026-02-03"]

    D["Start Date"] --> E["DATEDIFF()"]
    F["End Date"] --> E
    E --> G["Days Between\n(integer)"]

    H["Start Timestamp"] --> I["TIMESTAMPDIFF()"]
    J["End Timestamp"] --> I
    I --> K["Precision Difference\n(hours, minutes, etc.)"]

    classDef func fill:#0d6efd,stroke:#fff,color:#fff
    class E,I func

Quick Reference

-- Add/subtract intervals
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
SELECT NOW() + INTERVAL 1 YEAR;
SELECT NOW() - INTERVAL 24 HOUR;

-- Difference in days
SELECT DATEDIFF('2026-12-31', '2026-01-01');

-- Difference in any unit
SELECT TIMESTAMPDIFF(HOUR, start_at, end_at) FROM jobs;
SELECT TIMESTAMPDIFF(MINUTE, created_at, resolved_at) FROM tickets;
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) FROM users;

-- Common patterns
WHERE created_at >= NOW() - INTERVAL 7 DAY      -- last 7 days
WHERE created_at < NOW() - INTERVAL 90 DAY       -- older than 90 days
WHERE due_date < CURDATE()                        -- overdue

What's Next

12. Views

Concept Overview​

What Is Date Arithmetic?​

Key Functions​

Available Interval Units​

Basic Syntax & Rules​

Adding and Subtracting Intervals​

Measuring Differences​

Important: DATEDIFF vs. TIMESTAMPDIFF​

Step-by-Step Examples​

Example 1: Subscription Renewal Dates​

Example 2: Delivery Time Tracking​

Example 3: SLA Compliance Check​

Example 4: Data Retention — Purge Old Records​

Example 5: Age Calculation​

Practical Use Cases​

1. Billing and Invoicing​

2. SLA Monitoring​

3. Retention Policies​

4. Subscription Management​

5. Analytics and Reporting​

Common Mistakes & Troubleshooting​

Best Practices​

Hands-On Practice​

Exercise 1: Renewal Date Calculator (Easy)​

Exercise 2: SLA Report (Medium)​

Exercise 3: Retention Cleanup Script (Advanced)​

Connection to Other Concepts​

What to Learn Next​

Visual Learning Diagram​

Quick Reference​

What's Next​