MySQL Interval

Difficulty

Last Reviewed

Notes

Reference Link

Reviewed

Status

Understanding MySQL Interval Values

MySQL interval values are special expressions you use to perform date and time arithmetic. They allow you to add or subtract a specific amount of time (like days, months, or minutes) to a date or timestamp.

Basic Syntax

The general format is:

INTERVAL expr unit

expr: A number that represents the time value (for example, 1, 30, etc.).
unit: The time measurement (such as DAY, MONTH, MINUTE). This part isn’t case-sensitive, so DAY is the same as day.

For example, to create a time period of one day, you write:

INTERVAL 1 DAY

Using Intervals with Dates

You can use these interval values directly in arithmetic operations with dates:

Adding Time:

date + INTERVAL expr unit

Subtracting Time:

date - INTERVAL expr unit

Examples

Adding One Day:

This query adds one day to January 1, 2020.

SELECT '2020-01-01' + INTERVAL 1 DAY;

Result: 2020-01-02

Subtracting One Day:

You can also subtract time by using a negative value:

SELECT '2020-01-01' + INTERVAL -1 DAY;

Result: 2019-12-31

Using MySQL Date and Time Functions with Intervals

MySQL provides several functions that work with interval values to manipulate dates and times:

DATE_ADD(): Adds an interval to a given date.

DATE_ADD(date, INTERVAL expr unit)

DATE_SUB(): Subtracts an interval from a given date.

sql
Copy
DATE_SUB(date, INTERVAL expr unit)

TIMESTAMPADD(): Adds an interval to a timestamp value.

sql
Copy
TIMESTAMPADD(unit, expr, date)

TIMESTAMPDIFF(): Computes the difference between two dates in the specified unit.

Examples

Adding/Subtracting a Month:

This query shows how to add and subtract one month from January 1, 2020.

sql
Copy
SELECT
    DATE_ADD('2020-01-01', INTERVAL 1 MONTH) AS 1_MONTH_LATER,
    DATE_SUB('2020-01-01', INTERVAL 1 MONTH) AS 1_MONTH_BEFORE;

Result:

1_MONTH_LATER: 2020-02-01
1_MONTH_BEFORE: 2019-12-01

Adding 30 Minutes to a Timestamp:

Here, the TIMESTAMPADD() function adds 30 minutes.

sql
Copy
SELECT TIMESTAMPADD(MINUTE, 30, '2020-01-01') AS 30_MINUTES_LATER;

Result: 2020-01-01 00:30:00

Practical Example: Memberships and Expiration Dates

Imagine you have a table called memberships to track user memberships. The table includes:

id: A unique identifier for each member.
email: The member’s email address.
plan: The type of membership plan.
expired_date: The date when the membership expires.

Creating the Table

sql
Copy
CREATE TABLE memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(355) NOT NULL,
    plan VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL
);

Inserting Sample Data

sql
Copy
INSERT INTO memberships(email, plan, expired_date)
VALUES ('john.doe@example.com', 'Gold', '2017-07-13'),
       ('jane.smith@example.com', 'Platinum', '2017-07-10'),
       ('david.corp@example.com', 'Silver', '2017-07-15'),
       ('julia.william@example.com', 'Gold', '2017-07-20'),
       ('peter.drucker@example.com', 'Silver', '2017-07-08');

Query: Finding Memberships Expiring Soon

Suppose today is July 6, 2017. To find all members whose memberships expire within the next 7 days, you can use:

sql
Copy
SELECT
    email,
    plan,
    expired_date,
    DATEDIFF(expired_date, '2017-07-06') AS remaining_days
FROM memberships
WHERE '2017-07-06' BETWEEN DATE_SUB(expired_date, INTERVAL 7 DAY) AND expired_date;

Explanation:

DATEDIFF(expired_date, '2017-07-06'): Calculates the number of days between today and the expiration date.
WHERE clause:

DATE_SUB(expired_date, INTERVAL 7 DAY) computes a date that is 7 days before the expiration date.
The condition checks if today's date falls between that computed date and the actual expiration date.This means it finds memberships that are going to expire within the next 7 days.

Conclusion

For a data scientist, mastering MySQL interval values is crucial for efficiently managing and analyzing time-based data. Whether you are calculating the difference between dates, scheduling events, or adjusting dates in your datasets, these interval expressions and functions (like DATE_ADD, DATE_SUB, and TIMESTAMPADD) provide powerful tools for manipulating date and time values.

By understanding and using these interval operations, you can enhance your SQL queries to better handle time series data, expiration tracking, and various other time-sensitive tasks.

This explanation and the examples should give you a solid foundation for applying MySQL interval values in your data analysis workflows.