Date Arithmetic and INTERVAL Operations
What you'll learn: How to perform calculations with dates by adding or subtracting time intervals, measuring differences between dates, and working with date ranges in SQL.
The Core Concept
Date arithmetic lets you manipulate dates just like numbers—add days to a date, subtract months, or calculate how many days separate two events. The INTERVAL keyword is your tool for specifying amounts of time (like "5 days" or "3 months") that you can add to or subtract from dates.
Think of it like a calendar: if today is January 15th and you add an interval of 10 days, you get January 25th. SQL handles all the complexity of varying month lengths, leap years, and calendar rules automatically.
Basic Operations
Adding intervals to dates:
SELECT
order_date,
order_date + INTERVAL 7 DAY AS expected_delivery,
order_date + INTERVAL 1 MONTH AS follow_up_date
FROM orders;
Subtracting intervals:
SELECT
event_date,
event_date - INTERVAL 3 DAY AS reminder_date,
event_date - INTERVAL 2 WEEK AS early_bird_deadline
FROM events;
Calculating date differences:
SELECT
customer_id,
last_purchase_date - first_purchase_date AS days_as_customer,
YEAR(last_purchase_date) - YEAR(first_purchase_date) AS year_difference
FROM customers;
Working with date ranges:
SELECT *
FROM sales
WHERE sale_date BETWEEN CURRENT_DATE - INTERVAL 30 DAY
AND CURRENT_DATE;
The syntax for intervals typically follows: INTERVAL value unit where units include DAY, MONTH, YEAR, HOUR, MINUTE, WEEK, etc.
Key Takeaway: Date arithmetic with INTERVAL operations allows you to shift dates forward or backward in time, calculate time spans between events, and filter data within specific date ranges—essential for time-based analysis and business logic.