MySQL - INTERVAL Operator

MySQL - The INTERVAL Operator

What is the INTERVAL Operator?

The INTERVAL operator in MySQL is a fundamental keyword used for performing date and time calculations. It allows you to add or subtract a specified period of time from a date, time, or datetime value. This is essential for a wide range of applications, from scheduling events and calculating deadlines to analyzing time-series data.

Instead of manually calculating the number of seconds in a day or days in a month, INTERVAL provides a readable, declarative way to express time durations.

Syntax

INTERVAL expression unit

Where:

• expression: A value or an expression that resolves to a number, indicating the quantity of the interval.
• unit: A keyword specifying the unit of time, such as DAY, HOUR, YEAR, MONTH, etc. Both INTERVAL and unit are case-insensitive.

Supported Units

MySQL supports a comprehensive set of units for intervals:

Unit	Description
MICROSECOND	Microseconds
SECOND	Seconds
MINUTE	Minutes
HOUR	Hours
DAY	Days
WEEK	Weeks
MONTH	Months
QUARTER	Quarters (3 months)
YEAR	Years
YEAR_MONTH	Composite unit, e.g., INTERVAL ‘1-6’ YEAR_MONTH (1 year, 6 months)
DAY_HOUR	Composite unit, e.g., INTERVAL ‘2 12’ DAY_HOUR (2 days, 12 hours)

Other composite units like DAY_MINUTE, HOUR_SECOND, etc., are also available.

Performing Date and Time Arithmetic

The INTERVAL operator is used with the + and - operators for addition and subtraction.

Example 1: Adding Days to a Date

SELECT '2024-01-15' + INTERVAL 10 DAY;

Result
2024-01-25

Example 2: Subtracting Hours from a Datetime

SELECT '2024-01-15 10:00:00' - INTERVAL 3 HOUR;

Result
2024-01-15 07:00:00

Example 3: Using a Composite Unit

SELECT '2024-03-01' + INTERVAL '1-2' YEAR_MONTH; -- Adds 1 year and 2 months

Result
2025-05-01

Usage with Date/Time Functions

While arithmetic operators are common, it’s often more explicit and readable to use dedicated functions like DATE_ADD(), DATE_SUB(), and TIMESTAMPADD().

Example with DATE_ADD() and DATE_SUB()

SELECT
    DATE_ADD('2024-01-31', INTERVAL 1 MONTH) AS next_month,
    DATE_SUB('2024-03-31', INTERVAL 1 MONTH) AS prev_month;

MySQL is smart about handling month-end dates. Adding one month to Jan 31st correctly results in Feb 29th (in a leap year) or Feb 28th.

next_month	prev_month
2024-02-29	2024-02-29

Practical Example: Subscription Management

Let’s model a table for a service with subscriptions that need renewal.

CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    plan VARCHAR(50),
    expiry_date DATE NOT NULL
);

INSERT INTO subscriptions (user_name, plan, expiry_date) VALUES
    ('Alice', 'Monthly', '2024-11-15'),
    ('Bob', 'Annual', '2025-01-20'),
    ('Charlie', 'Monthly', '2024-10-30'),
    ('David', 'Monthly', '2024-11-05');

Now, let’s find all subscriptions that will expire within the next 7 days from today’s date (CURDATE()).

-- Assuming CURDATE() is '2024-10-29'
SELECT user_name, plan, expiry_date
FROM subscriptions
WHERE expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

This query would select Charlie and David.

We can also use INTERVAL to update the expiry dates when a user renews their plan.

-- Alice renews her Monthly plan
UPDATE subscriptions
SET expiry_date = DATE_ADD(expiry_date, INTERVAL 1 MONTH)
WHERE user_name = 'Alice';

Using INTERVAL in Client Applications

Using INTERVAL from a client application is simple. You can either build the query string directly (if the interval is static) or use parameters for the interval value.

PHP (PDO)
Node.js (mysql2/promise)
Java (JDBC)
Python (mysql-connector-python)




```php
<?php
// Assuming $pdo is a connected PDO object

// Renewing a subscription by a dynamic number of months
$userName = 'Bob';
$monthsToAdd = 12; // From an 'Annual' plan purchase

$sql = "UPDATE subscriptions
        SET expiry_date = DATE_ADD(expiry_date, INTERVAL ? MONTH)
        WHERE user_name = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute([$monthsToAdd, $userName]);

echo "Subscription for {$userName} renewed successfully.";
?>

main.js

const mysql = require('mysql2/promise');

async function renewSubscription() {
    let connection;
    try {
        connection = await mysql.createConnection({ /* connection config */ });

        const userName = 'Bob';
        const monthsToAdd = 12;

        const sql = 'UPDATE subscriptions SET expiry_date = DATE_ADD(expiry_date, INTERVAL ? MONTH) WHERE user_name = ?';
        const [result] = await connection.execute(sql, [monthsToAdd, userName]);

        if (result.affectedRows > 0) {
            console.log(`Subscription for ${userName} renewed successfully.`);
        } else {
            console.log(`User ${userName} not found.`);
        }
    } catch (error) {
        console.error('Error renewing subscription:', error);
    } finally {
        if (connection) await connection.end();
    }
}

renewSubscription();

SubscriptionManager.java

import java.sql.*;

public class SubscriptionManager {
    // Assuming DB_URL, USER, PASS are defined
    public static void main(String[] args) {
        String sql = "UPDATE subscriptions SET expiry_date = DATE_ADD(expiry_date, INTERVAL ? MONTH) WHERE user_name = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int monthsToAdd = 12;
            String userName = "Bob";

            pstmt.setInt(1, monthsToAdd);
            pstmt.setString(2, userName);

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Subscription for " + userName + " renewed successfully.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

renew_script.py

import mysql.connector

def renew_subscription(user_name, months_to_add):
    try:
        conn = mysql.connector.connect(/* connection config */)
        cursor = conn.cursor()

        sql = ("UPDATE subscriptions "
               "SET expiry_date = DATE_ADD(expiry_date, INTERVAL %s MONTH) "
               "WHERE user_name = %s")

        cursor.execute(sql, (months_to_add, user_name))
        conn.commit()

        if cursor.rowcount > 0:
            print(f"Subscription for {user_name} renewed successfully.")
        else:
            print(f"User {user_name} not found.")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    renew_subscription('Bob', 12)

## Important Considerations

- **Time Zones:** MySQL's date and time functions can be time zone-aware, but this depends on the server's and client's time zone settings. For global applications, it's best practice to store all datetimes in UTC and perform conversions to local time zones within the application layer.
- **Leap Years and Month Ends:** MySQL correctly handles complexities like leap years and the varying number of days in a month when using `MONTH` or `YEAR` intervals. Adding `INTERVAL 1 MONTH` to `2024-01-31` correctly yields `2024-02-29`.
- **Ambiguity:** Be careful with units. `INTERVAL 30 DAY` is not always the same as `INTERVAL 1 MONTH`. The former always adds exactly 30 days, while the latter adjusts for the specific month.