SQL Date Functions: DATE_ADD, DATEDIFF, EXTRACT

Key Insights

• DATE_ADD, DATEDIFF, and EXTRACT are fundamental SQL functions that handle 90% of date manipulation tasks, from calculating subscription renewals to analyzing time-series patterns
• Each major database system (MySQL, PostgreSQL, SQL Server) implements these functions differently—understanding the variations prevents deployment headaches and enables writing portable queries
• Combining date functions with proper indexing strategies can make or break query performance; always filter on indexed date columns rather than wrapping them in functions

Introduction to Date Manipulation in SQL

Date manipulation sits at the core of most business applications. Whether you’re calculating when a subscription expires, determining how long customers stay active, or grouping sales by quarter, you need reliable date functions. The three workhorses of SQL date handling—DATE_ADD, DATEDIFF, and EXTRACT—cover the vast majority of real-world scenarios.

These functions aren’t just convenient; they’re essential for data integrity. Attempting to manipulate dates with string operations or manual calculations leads to bugs around time zones, leap years, and month boundaries. Let the database handle the complexity.

DATE_ADD: Adding Time Intervals to Dates

DATE_ADD allows you to add (or subtract with negative values) time intervals to dates. The basic syntax in MySQL looks like this:

DATE_ADD(date, INTERVAL value unit)

The unit can be DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, or combinations like DAY_HOUR.

Here’s how to calculate subscription expiration dates:

SELECT 
    user_id,
    subscription_start,
    DATE_ADD(subscription_start, INTERVAL 1 YEAR) AS expiration_date
FROM subscriptions
WHERE plan_type = 'annual';

For monthly subscriptions with a grace period:

SELECT 
    user_id,
    DATE_ADD(subscription_start, INTERVAL 1 MONTH) AS renewal_date,
    DATE_ADD(subscription_start, INTERVAL 1 MONTH + 3 DAY) AS grace_period_end
FROM subscriptions
WHERE plan_type = 'monthly';

Adding business days requires more logic since SQL doesn’t natively understand weekends. Here’s a practical approach for adding 5 business days:

SELECT 
    order_id,
    order_date,
    DATE_ADD(
        order_date,
        INTERVAL (5 + 
            CASE WHEN DAYOFWEEK(order_date) = 6 THEN 2  -- Friday
                 WHEN DAYOFWEEK(order_date) = 7 THEN 1  -- Saturday
                 ELSE 0 
            END
        ) DAY
    ) AS estimated_delivery
FROM orders;

This adds extra days when the order falls on a weekend to account for non-business days.

Subtracting time works identically with negative intervals:

-- Find records created in the last 7 days
SELECT *
FROM events
WHERE event_date >= DATE_ADD(CURRENT_DATE, INTERVAL -7 DAY);

DATEDIFF: Calculating Time Between Dates

DATEDIFF computes the difference between two dates. In MySQL, it returns the number of days:

DATEDIFF(date1, date2)

The result is date1 - date2 in days. This is perfect for calculating customer lifetime:

SELECT 
    customer_id,
    first_purchase_date,
    last_purchase_date,
    DATEDIFF(last_purchase_date, first_purchase_date) AS customer_lifetime_days
FROM customer_activity;

Finding overdue invoices becomes straightforward:

SELECT 
    invoice_id,
    due_date,
    DATEDIFF(CURRENT_DATE, due_date) AS days_overdue
FROM invoices
WHERE DATEDIFF(CURRENT_DATE, due_date) > 0
AND status = 'unpaid';

Computing age from birthdate:

SELECT 
    user_id,
    birthdate,
    FLOOR(DATEDIFF(CURRENT_DATE, birthdate) / 365.25) AS age_years
FROM users;

Note the division by 365.25 to account for leap years. For more precise age calculations, use DATE_ADD in combination:

SELECT 
    user_id,
    TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE) AS age_years
FROM users;

TIMESTAMPDIFF is MySQL’s function for calculating differences in specific units (YEAR, MONTH, DAY, HOUR, etc.).

EXTRACT: Pulling Specific Date Components

EXTRACT pulls individual components from date or timestamp values. The syntax is standardized across most databases:

EXTRACT(unit FROM date)

Extracting years for annual reports:

SELECT 
    EXTRACT(YEAR FROM sale_date) AS sale_year,
    SUM(amount) AS total_sales
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date)
ORDER BY sale_year;

Getting the day of week for pattern analysis:

SELECT 
    EXTRACT(DOW FROM order_timestamp) AS day_of_week,
    COUNT(*) AS order_count,
    AVG(order_total) AS avg_order_value
FROM orders
GROUP BY EXTRACT(DOW FROM order_timestamp)
ORDER BY day_of_week;

In PostgreSQL, DOW returns 0 for Sunday through 6 for Saturday. MySQL uses DAYOFWEEK() instead, returning 1-7.

Pulling hours for hourly metrics:

SELECT 
    EXTRACT(HOUR FROM event_timestamp) AS hour_of_day,
    COUNT(*) AS event_count
FROM user_events
WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY EXTRACT(HOUR FROM event_timestamp)
ORDER BY hour_of_day;

Combining Date Functions for Complex Queries

Real-world queries often require multiple date functions working together. Here’s a query finding active users in the last 30 days:

SELECT 
    user_id,
    MAX(login_date) AS last_login,
    DATEDIFF(CURRENT_DATE, MAX(login_date)) AS days_since_login
FROM user_logins
GROUP BY user_id
HAVING DATEDIFF(CURRENT_DATE, MAX(login_date)) <= 30;

Calculating quarterly metrics with EXTRACT and DATE_ADD:

SELECT 
    EXTRACT(YEAR FROM sale_date) AS year,
    EXTRACT(QUARTER FROM sale_date) AS quarter,
    COUNT(*) AS total_sales,
    SUM(amount) AS revenue
FROM sales
WHERE sale_date >= DATE_ADD(CURRENT_DATE, INTERVAL -2 YEAR)
GROUP BY 
    EXTRACT(YEAR FROM sale_date),
    EXTRACT(QUARTER FROM sale_date)
ORDER BY year, quarter;

Building a dynamic date range filter:

SELECT *
FROM orders
WHERE order_date BETWEEN 
    DATE_ADD(CURRENT_DATE, INTERVAL -1 MONTH) 
    AND CURRENT_DATE
AND EXTRACT(DOW FROM order_date) NOT IN (0, 6)  -- Exclude weekends
AND DATEDIFF(CURRENT_DATE, order_date) >= 7;    -- At least a week old

Database-Specific Considerations and Best Practices

Each database system has quirks. Here’s how the same operations look across platforms:

PostgreSQL uses different function names:

-- Adding intervals
SELECT order_date + INTERVAL '30 days' AS due_date FROM orders;

-- Date difference
SELECT AGE(CURRENT_DATE, order_date) FROM orders;  -- Returns interval
SELECT CURRENT_DATE - order_date AS days_diff FROM orders;  -- Returns integer

-- Extract
SELECT EXTRACT(YEAR FROM order_date) FROM orders;

SQL Server syntax:

-- Adding intervals
SELECT DATEADD(day, 30, order_date) AS due_date FROM orders;

-- Date difference
SELECT DATEDIFF(day, order_date, GETDATE()) AS days_diff FROM orders;

-- Extract (use DATEPART)
SELECT DATEPART(year, order_date) FROM orders;

Performance considerations: Never wrap indexed date columns in functions when filtering:

-- BAD: Prevents index usage
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

-- GOOD: Uses index
SELECT * FROM orders
WHERE order_date >= '2024-01-01' 
AND order_date < '2025-01-01';

Always use UTC for stored timestamps and convert to local time zones only in the application layer or final SELECT. Mixing time zones in WHERE clauses causes subtle bugs.

Index your date columns, especially those frequently used in WHERE clauses and JOINs. Consider partial indexes for common date ranges:

-- PostgreSQL partial index
CREATE INDEX idx_recent_orders 
ON orders(order_date)
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days';

Quick Reference

Master these three functions and their database-specific variations, and you’ll handle 90% of date manipulation tasks efficiently. Remember to keep date logic in the database where it belongs—attempting to handle dates in application code leads to time zone bugs and inconsistent data. Let SQL do what it does best.