Course contentsShow
← Data Science
Lesson 1040 of 2,14526. Advanced SQLFree lesson

Date Arithmetic and INTERVAL Operations

Adding and subtracting time intervals from dates, calculating date differences, and working with date ranges.

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.