INTERVAL Operator in MySQL
Introduction
Time-based filtering requires relative date calculations beyond fixed dates.
The INTERVAL operator adds/subtracts time periods to dates in MySQL.
It is widely used for:
- Recent data filtering
- Date range calculations
- Activity window queries
What is the INTERVAL Operator
INTERVAL specifies time duration (days, months, years) for date arithmetic.
Used with DATE_ADD(), DATE_SUB(), and comparison operators.
Basic Syntax
sql
DATE_ADD(date, INTERVAL expr unit);
DATE_SUB(date, INTERVAL expr unit);
column BETWEEN date AND DATE_ADD(date, INTERVAL 30 DAY);
Example
sql
SELECT * FROM Students
WHERE join_date > DATE_SUB(NOW(), INTERVAL 30 DAY);
Students joined in the last 30 days.
INTERVAL Units
Common units: YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND
DATE_ADD Examples
sql
SELECT DATE_ADD('2025-01-15', INTERVAL 1 MONTH); -- 2025-02-15
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- Next week
SELECT DATE_ADD('2025-01-15 10:30:00', INTERVAL 2 HOUR); -- 12:30
DATE_SUB Examples
sql
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR); -- Same date last year
SELECT DATE_SUB('2025-12-25', INTERVAL 1 MONTH); -- 2025-11-25
INTERVAL with BETWEEN
Relative date ranges.
sql
SELECT * FROM Orders
WHERE order_date BETWEEN
DATE_SUB(NOW(), INTERVAL 90 DAY) AND
NOW();
INTERVAL with WHERE Conditions
Recent activity filtering.
sql
SELECT * FROM Students
WHERE last_login > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND status = 'Active';
INTERVAL with ORDER BY
Recent first.
sql
SELECT * FROM Logs
WHERE log_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY log_date DESC;
INTERVAL with GROUP BY
Time period analysis.
sql
SELECT
DATE(log_date) as date,
COUNT(*) as log_count
FROM Logs
WHERE log_date > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(log_date)
ORDER BY date DESC;
INTERVAL with JOIN
Time-based relationships.
sql
SELECT s.name, o.order_date
FROM Students s
JOIN Orders o ON s.id = o.student_id
WHERE o.order_date > DATE_SUB(NOW(), INTERVAL 14 DAY);
Multiple INTERVAL Units
Complex time calculations.
sql
SELECT * FROM Students
WHERE DATEDIFF(NOW(), join_date) BETWEEN 30 AND INTERVAL 2 MONTH;
INTERVAL in Subqueries
Dynamic time windows.
sql
SELECT * FROM Students s
WHERE s.marks > (
SELECT AVG(marks)
FROM Students
WHERE join_date > DATE_SUB(s.join_date, INTERVAL 1 YEAR)
);
Performance with INTERVAL
searchable (Search ARGument able) queries.
sql
-- GOOD (uses index)
WHERE order_date > DATE_SUB(NOW(), INTERVAL 30 DAY);
-- BAD (function on column, no index)
WHERE DATE(order_date) > DATE_SUB(NOW(), INTERVAL 30 DAY);
Execution Order with INTERVAL
Evaluated in WHERE after FROM.
FROM → WHERE (INTERVAL calcs) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Performance Considerations
- Functions on indexed columns prevent index use
- Pre-calculate common date ranges
- Use indexed date columns
- INTERVAL arithmetic is very fast
Important Notes
- INTERVAL 1 DAY ≠ 24 HOURS (month lengths vary)
- DATE_ADD handles month-end overflow
- Works with DATE, DATETIME, TIMESTAMP
- Negative INTERVAL subtracts time
- MySQL-specific syntax (standard SQL differs)
Example Scenario
Find recent highly active students:
sql
SELECT name, marks, join_date, last_login
FROM Students
WHERE join_date > DATE_SUB(NOW(), INTERVAL 90 DAY)
AND last_login > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND marks IS NOT NULL
ORDER BY last_login DESC, marks DESC
LIMIT 50;
Common Mistakes
- Applying functions to indexed columns
- INTERVAL DAY vs HOUR confusion
- Month-end date overflow surprises
- Negative INTERVAL syntax errors
- Performance from DATE() on datetime columns
Key Points to Remember
- INTERVAL for relative date/time arithmetic
- DATE_ADD() / DATE_SUB() with INTERVAL
- Keep functions off indexed columns
- Multiple units: YEAR/MONTH/DAY/HOUR
- sargable queries use indexes
- Handles month-end overflow automatically