Master MySQL Date Operations: DATE_ADD and INTERVAL Guide

1. Introduction

MySQL is a widely used RDBMS (Relational Database Management System) in web applications and database management. Among its features, date operations are one of the important functions that many developers use on a daily basis. For example, there are numerous scenarios involving dates, such as setting periodic reminders, extracting data within a specific period, and calculating schedules.

Especially, the function to add or subtract dates is used frequently. MySQL provides convenient functions like DATE_ADD and DATE_SUB, as well as the INTERVAL clause to specify periods. By utilizing these, you can keep your code concise while efficiently performing complex date calculations.

In this article, we will explain date addition and subtraction in MySQL in an easy-to-understand way for beginners. We will cover a wide range from basic usage to practical examples, providing know-how useful in real-world scenarios. Additionally, we will discuss common problems and precautions that you might encounter, so even those who lack confidence in date operations can learn with peace of mind.

By the time you finish reading this article, you will have acquired the necessary knowledge and practical techniques for date operations in MySQL, and you will be able to apply them to your own projects.

2. Basic Knowledge Required for Date Operations in MySQL

When handling dates in MySQL, it is important to first understand date data types and basic concepts. This section explains the basic knowledge necessary for date operations in MySQL.

Types of Date Data Types

MySQL provides several data types for handling dates and times. It is important to understand the characteristics of each data type and select the appropriate one.

1. DATE Type

• Records year, month, and day.
• Format: YYYY-MM-DD
• Example: 2025-01-01
• Mainly used when only the date is needed (e.g., birthday, creation date).

1. DATETIME Type

• Records year, month, day, and time.
• Format: YYYY-MM-DD HH:MM:SS
• Example: 2025-01-01 12:30:45
• Used when both date and time are needed (e.g., event date and time).

1. TIMESTAMP Type

• Records date and time based on UTC (Coordinated Universal Time), allowing timezone conversion.
• Format: YYYY-MM-DD HH:MM:SS
• Example: 2025-01-01 12:30:45
• Used when timezone-aware data is needed or for system logs.

1. TIME Type

• Records time.
• Format: HH:MM:SS
• Example: 12:30:45
• Used when pure time data is needed (e.g., business hours).

1. YEAR Type

• Records only the year.
• Format: YYYY
• Example: 2025
• Used for managing data on a yearly basis.

Precautions When Using Date Types

• Timezone Settings
  MySQL uses the server’s timezone settings by default. However, if the application uses a different timezone, data inconsistencies may occur. Please explicitly set the timezone as needed.
• Handling Invalid Dates
  By default in MySQL, specifying an invalid date (e.g., 2025-02-30) results in an automatic error. However, depending on server settings, it may be converted to NULL or a default value, so it is important to check the settings.

Main Use Cases for Date Operations

1. Extracting Data for a Specific Period

• Example: Extract sales data from the past week.

1. Calculating Schedules

• Example: Send a notification 30 days after the user registration date.

1. Managing Data Expiration

• Example: Manage the expiration dates of coupons.

Summary of Basic Knowledge

MySQL is equipped with powerful tools for handling dates and times. By understanding the characteristics of date types and selecting them appropriately, you can improve database design and query efficiency. In the next section, we will explain the DATE_ADD function in detail as a specific method for date operations.

3. Basics and Applications of the DATE_ADD Function

MySQL’s DATE_ADD function is a convenient function used to add a specified period to a date. This section explains the basic usage of the DATE_ADD function as well as application examples.

What is the DATE_ADD Function?

The DATE_ADD function adds the period specified in the INTERVAL clause to the given date and returns a new date. This function is a basic tool for simplifying date calculations.

Basic Syntax:

DATE_ADD(date, INTERVAL value unit)

• date: The date or datetime to operate on.
• value: The numeric value to add.
• unit: The unit of the interval to add (e.g., DAY, MONTH, YEAR, HOUR, MINUTE, SECOND).

Available INTERVAL Units

The following INTERVAL units can be used with the DATE_ADD function.

Basic Usage Examples

The following shows specific examples of the DATE_ADD function.

1. Calculating 1 Day Later:

SELECT DATE_ADD('2025-01-01', INTERVAL 1 DAY);

Result: 2025-01-02

2. Calculating 1 Month Later:

SELECT DATE_ADD('2025-01-01', INTERVAL 1 MONTH);

Result: 2025-02-01

3. Calculating 1 Year Later:

SELECT DATE_ADD('2025-01-01', INTERVAL 1 YEAR);

Result: 2026-01-01

4. Calculating 3 Hours Later:

SELECT DATE_ADD('2025-01-01 12:00:00', INTERVAL 3 HOUR);

Result: 2025-01-01 15:00:00

5. Adding Multiple Units (Using Nesting):

SELECT DATE_ADD(DATE_ADD('2025-01-01', INTERVAL 1 DAY), INTERVAL 3 HOUR);

Result: 2025-01-02 03:00:00

Dynamic Date Calculations

The DATE_ADD function can also be applied to dynamically calculated dates.

1. Calculating 7 Days from Today:

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY);

Result: The date 7 days from the current date.

2. Calculating 30 Minutes from the Current Time:

SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);

Result: The datetime 30 minutes from the current datetime.

Application Examples: Practical Usage

1. Calculating Expiration Dates:
   Calculate a 30-day warranty period from the product purchase date.

SELECT DATE_ADD(purchase_date, INTERVAL 30 DAY) AS expiry_date
FROM orders;

2. Sending Reservation Reminders:
   Calculate 3 days before the event start date and send a reminder.

SELECT DATE_ADD(event_date, INTERVAL -3 DAY) AS reminder_date
FROM events;

3. Calculating Log Retention Periods:
   Calculate 90 days before the current date and delete logs prior to that.

DELETE FROM logs
WHERE log_date < DATE_ADD(CURDATE(), INTERVAL -90 DAY);

Notes

• Handling Invalid Dates:
  If the result of the DATE_ADD function operation results in an invalid date, MySQL may return NULL. Please verify in advance that the input data is in the correct format.
• Impact of Time Zones:
  If using time zones, the results of CURRENT_TIMESTAMP or NOW() may be affected.

The DATE_ADD function is one of the most basic yet powerful tools for date operations in MySQL. Mastering this function will allow you to perform many date calculations efficiently. In the next section, we will explain the DATE_SUB function in detail.

4. Basics and Applications of the DATE_SUB Function

The DATE_SUB function is a MySQL feature that pairs with the DATE_ADD function and is used to subtract a specified period from a date. In this section, we will explain the basic usage of the DATE_SUB function and practical application examples that are useful in real-world scenarios.

What is the DATE_SUB Function?

The DATE_SUB function subtracts the period specified in the INTERVAL clause from the specified date or datetime and returns a new date. It is a convenient tool for subtraction operations in date manipulations.

Basic Syntax:

DATE_SUB(date, INTERVAL value unit)

• date: The date or datetime to operate on.
• value: The numerical value to subtract.
• unit: The unit of the period to subtract (e.g., DAY, MONTH, YEAR, HOUR, MINUTE, SECOND).

Basic Usage Examples

The following shows specific examples of the DATE_SUB function.

1. Calculate 1 Day Ago:

SELECT DATE_SUB('2025-01-01', INTERVAL 1 DAY);

Result: 2024-12-31

2. Calculate 1 Month Ago:

SELECT DATE_SUB('2025-01-01', INTERVAL 1 MONTH);

Result: 2024-12-01

3. Calculate 1 Year Ago:

SELECT DATE_SUB('2025-01-01', INTERVAL 1 YEAR);

Result: 2024-01-01

4. Calculate 3 Hours Ago:

SELECT DATE_SUB('2025-01-01 12:00:00', INTERVAL 3 HOUR);

Result: 2025-01-01 09:00:00

5. Subtracting Multiple Units (Using Nesting):

SELECT DATE_SUB(DATE_SUB('2025-01-01', INTERVAL 1 DAY), INTERVAL 3 HOUR);

Result: 2024-12-31 21:00:00

Dynamic Date Calculations

It is also possible to use the DATE_SUB function based on dynamic dates.

1. Calculate 7 Days Ago from Today:

SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY);

Result: The date 7 days before the current date.

2. Calculate 30 Minutes Ago from the Current Time:

SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE);

Result: 30 minutes before the current datetime.

Application Examples: Practical Usage

1. Extract Data from the Past 30 Days:

SELECT * 
FROM orders
WHERE order_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY);

2. Delete Data from 90 Days Ago:

DELETE FROM logs
WHERE log_date < DATE_SUB(CURDATE(), INTERVAL 90 DAY);

3. Reminder Notifications:
   Set a reminder one day before the event starts.

SELECT event_name, DATE_SUB(event_date, INTERVAL 1 DAY) AS reminder_date
FROM events;

4. Shift Calculations:
   Calculate 3 hours before based on the work shift start time.

SELECT DATE_SUB(shift_start, INTERVAL 3 HOUR) AS preparation_time
FROM work_shifts;

Notes

• Handling Invalid Dates:
  If the subtraction result is not a valid date, MySQL may return NULL. Therefore, it is important to verify the validity of the original date.
• Impact of Time Zones:
  If the server’s time zone settings differ, discrepancies may occur in the results obtained from DATE_SUB. Use the CONVERT_TZ() function as needed.

The DATE_SUB function is an important tool in MySQL date operations, just like the DATE_ADD function. By performing subtraction operations concisely, data management and analysis become more efficient. In the next section, we will explain “5. Details and Usage of the INTERVAL Clause.”

5. Details and Usage of the INTERVAL Clause

The INTERVAL clause is a clause used in MySQL to manipulate dates and times, and it is used in combination with the DATE_ADD and DATE_SUB functions to add or subtract periods. In this section, we will explain the INTERVAL clause in detail from the basics to advanced usage.

Basics of the INTERVAL Clause

The INTERVAL clause is a clause used to specify how much period to operate on the target date. It is often used together with the DATE_ADD or DATE_SUB functions, allowing powerful date operations with concise syntax.

Basic Syntax:

SELECT date + INTERVAL value unit;
SELECT date - INTERVAL value unit;

• date: The target date or datetime.
• value: The value to add or subtract.
• unit: The unit of operation (e.g., DAY, MONTH, YEAR, HOUR, MINUTE, SECOND).

Available Units

The units that can be used in the INTERVAL clause are as follows.

Basic Usage Examples

1. Add 1 Day to a Date:

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

Result: 2025-01-02

2. Subtract 3 Hours from a Datetime:

SELECT '2025-01-01 12:00:00' - INTERVAL 3 HOUR;

Result: 2025-01-01 09:00:00

3. Subtract 10 Minutes from the Current Datetime:

SELECT NOW() - INTERVAL 10 MINUTE;

4. Calculate the First Day of the Next Month from the End of the Month:

SELECT LAST_DAY('2025-01-01') + INTERVAL 1 DAY;

Result: The 1st of the next month.

Advanced Examples

1. Date Range Calculation
   Calculate the range for the past 30 days.

SELECT *
FROM orders
WHERE order_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();

2. Calculate the Start Date of the Next Quarter

SELECT '2025-01-01' + INTERVAL 1 QUARTER;

Result: The start date of the next quarter.

3. Time Calculation with Timezone Support
   Obtain 1 hour after calculated in the server’s default timezone.

SELECT CONVERT_TZ(NOW(), 'SYSTEM', '+09:00') + INTERVAL 1 HOUR;

4. Perform Complex Calculations
   Calculate the date 2 weeks from today and obtain the last day of that month.

SELECT LAST_DAY(CURDATE() + INTERVAL 14 DAY);

Precautions

1. Be Careful with the Type of Calculation Results
   The calculation result of the INTERVAL clause maintains the original type (DATE type or DATETIME type). Please handle the data types correctly.
2. Handling of End-of-Month Dates
   When adding or subtracting on the end-of-month date, it may be adjusted to the end or beginning of the next month. For example, adding 1 month to 2025-01-31 results in 2025-02-28 (if not a leap year).
3. Impact of Timezones
   If different timezones are set on the server and client, discrepancies may occur in the datetime calculation results.

The INTERVAL clause is an important tool that simplifies date operations in MySQL. By leveraging its flexibility, complex date calculations can be performed easily. In the next section, we will explain “6. Practical Application Examples.”

6. Practical Application Examples

Using MySQL’s DATE_ADD function and INTERVAL clause makes complex date calculations and dynamic processing easy. In this section, we introduce several practical application examples that are useful in real-world scenarios.

1. Extracting Data Within a Specific Period

In data analysis or report creation, it is common to extract data recorded within a specific period.

Example 1: Retrieve Sales Data for the Past 30 Days

SELECT *
FROM sales
WHERE sale_date >= CURDATE() - INTERVAL 30 DAY;

Explanation: This extracts data from the date 30 days prior to the current date onward.

Example 2: Retrieve Last Month’s Data

SELECT *
FROM orders
WHERE order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE();

Explanation: This retrieves order data from the previous month.

2. Setting Event Reminders

You can set reminder notifications based on event start dates or deadlines.

Example: Send Notification 3 Days Before Event Start

SELECT event_name, DATE_SUB(event_date, INTERVAL 3 DAY) AS reminder_date
FROM events;

Explanation: This calculates the date 3 days before the event start and sets it as the notification date.

3. Managing Expiry Dates

Processing to calculate expiry dates based on registration dates is used in many applications.

Example 1: Calculate Expiry Date 30 Days After Registration

SELECT user_id, registration_date, DATE_ADD(registration_date, INTERVAL 30 DAY) AS expiry_date
FROM users;

Explanation: This calculates the date 30 days after registration as the expiry date.

Example 2: Delete Expired Data

DELETE FROM sessions
WHERE expiry_date < NOW();

Explanation: This deletes session data whose expiry date has passed, based on the current date and time.

4. Calculating Shifts or Schedules

This is an example of adjusting times based on start or end times in work hours or schedule management.

Example: Calculate 1 Hour Before Shift Start Time

SELECT shift_id, shift_start, DATE_SUB(shift_start, INTERVAL 1 HOUR) AS preparation_time
FROM shifts;

Explanation: This calculates 1 hour before the shift start time and displays it as preparation time.

5. Combining Multiple INTERVALs in Calculations

This is an example of combining multiple periods in calculations.

Example 1: Retrieve the End of the Month 1 Month from Today

SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH);

Explanation: This calculates the end of the month 1 month from the current date.

Example 2: Add 3 Months of Grace Period 1 Year After Registration

SELECT user_id, DATE_ADD(DATE_ADD(registration_date, INTERVAL 1 YEAR), INTERVAL 3 MONTH) AS final_deadline
FROM users;

Explanation: This calculates a grace period of 3 months starting 1 year after the registration date.

6. Data Cleaning and Optimization

This is an example of optimizing the database by deleting old data.

Example: Delete Users with No Access in the Past 90 Days

DELETE FROM user_activity
WHERE last_login < CURDATE() - INTERVAL 90 DAY;

Explanation: This deletes users with no login history in the past 90 days.

Notes

• Verification of Calculation Results: Pay particular attention to boundary conditions for dates, such as month-ends or leap years.
• Data Type Consistency: Specify date types (DATE, DATETIME, etc.) correctly to prevent errors.
• Time Zone Considerations: When operating across different time zones, use the CONVERT_TZ() function.

By applying the DATE_ADD function and INTERVAL clause, you can easily and efficiently handle date operations in real-world tasks. The next section provides a detailed explanation of “7. Notes and Best Practices.”

7. Precautions and Best Practices

Date operations in MySQL are highly convenient, but when using them in real-world applications, it is essential to understand several precautions and best practices. This section explains key points to watch out for during date operations and methods for efficient usage.

Precautions

1. Data Type Consistency

• Issue: In MySQL, results may vary depending on the data types used for date and time operations (such as DATE, DATETIME, TIMESTAMP, etc.).
• Example:

  SELECT DATE_ADD('2025-01-01', INTERVAL 1 DAY); -- Valid operation
  SELECT DATE_ADD('Invalid Date', INTERVAL 1 DAY); -- Returns NULL for invalid date

• Countermeasure: Verify in advance whether the input data is in the correct format and select the appropriate data type.

2. Handling Invalid Dates

• Issue: Calculations involving end-of-month dates or leap years may produce invalid dates.
• Example:

  SELECT DATE_ADD('2025-01-31', INTERVAL 1 MONTH); -- Result: 2025-02-28

In this case, MySQL automatically adjusts it, but the result may differ from what was intended.

• Countermeasure: When handling end-of-month dates, use the LAST_DAY() function for verification.

  SELECT LAST_DAY('2025-01-31') + INTERVAL 1 MONTH;

3. Considering Time Zones

• Issue: If the server’s time zone differs from the application’s time zone, data inconsistencies or unexpected results may occur.
• Example:

  SELECT NOW(); -- Depends on the server's time zone

• Countermeasure: Explicitly set the time zone.

  SELECT CONVERT_TZ(NOW(), 'SYSTEM', '+09:00'); -- Convert to Japan time

4. Performance in Complex Calculations

• Issue: Queries involving complex date calculations may affect execution speed.
• Countermeasure: Reduce unnecessary calculations and optimize queries by utilizing indexes.

Best Practices

1. Use Standard Formats

• Maintain consistency in date formats.
• Recommended formats: YYYY-MM-DD (DATE type), YYYY-MM-DD HH:MM:SS (DATETIME type).
• Example:

  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

2. Leverage Function Combinations

• Combining DATE_ADD or DATE_SUB with LAST_DAY or CURDATE enables more flexible calculations.
• Example:

  SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

3. Plan Dynamic Data Operations

• When manipulating dynamic dates, split the query and process it step by step.
• Example:

  SET @next_month = DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
  SELECT LAST_DAY(@next_month);

4. Secure Time Zone Management

• Unify time zones between the server and client.
• Example:

  SET time_zone = '+09:00';

5. Verification in Test Environments

• Especially for complex date calculations or calculations in different time zones, perform tests in advance.

Avoiding Pitfalls in Date Operations

Date operations are an important skill that greatly impacts database design and query accuracy. By understanding the precautions and following best practices, you can prevent issues and achieve efficient data operations.

8. FAQ

We have compiled frequently asked questions (FAQ) regarding MySQL date operations. We provide concise answers to points that beginners to intermediate users are likely to have questions about.

Q1: What is the difference between the DATE_ADD function and the INTERVAL clause using the + operator?

A1:

• DATE_ADD() function is a dedicated function for performing date calculations and excels in error handling and type conversion.
• The + INTERVAL clause allows for simple calculations but may cause type conversion errors.

Example: Using DATE_ADD:

SELECT DATE_ADD('2025-01-01', INTERVAL 1 DAY);

Example: Using the + operator and INTERVAL:

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

In general, using DATE_ADD() is recommended.

Q2: Can the DATE_ADD function add multiple intervals at the same time?

A2: No. The DATE_ADD() function can only specify one interval at a time. However, it is possible to handle it by nesting multiple DATE_ADD() functions.

Example: Adding multiple intervals:

SELECT DATE_ADD(DATE_ADD('2025-01-01', INTERVAL 1 DAY), INTERVAL 1 MONTH);

Q3: How can I change the date format in MySQL?

A3: You can change it to any format by using the DATE_FORMAT() function.

Example: Changing the format:

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s') AS formatted_date;

Result: 2025/01/01 12:30:45

Q4: What happens when adding 1 month to the last day of a month?

A4: In MySQL, it is automatically adjusted, and the result becomes the last day of the following month.

Example:

SELECT DATE_ADD('2025-01-31', INTERVAL 1 MONTH);

Result: 2025-02-28

This adjustment is convenient when handling date types, but it is important to confirm whether it is the intended result.

Q5: How can I perform date calculations considering time zones?

A5: You can handle it by specifying the time zone using MySQL’s CONVERT_TZ() function.

Example: Converting from UTC to Japan time:

SELECT CONVERT_TZ('2025-01-01 12:00:00', 'UTC', '+09:00') AS japan_time;

Q6: What happens if an invalid date is specified for the DATE_ADD function?

A6: If an invalid date (e.g., 2025-02-30) is specified, MySQL returns NULL.

Example:

SELECT DATE_ADD('2025-02-30', INTERVAL 1 DAY);

Result: NULL

It is recommended to validate input values in advance.

Q7: How can I set conditions based on past or future dates?

A7: Use DATE_ADD or DATE_SUB to calculate the base date and use the result in the conditional expression.

Example: Extracting data from the past 30 days:

SELECT * 
FROM sales
WHERE sale_date >= CURDATE() - INTERVAL 30 DAY;

Q8: What are the best practices when using multiple time zones?

A8:

• Store all date and time data in UTC.
• Convert time zones on the client side as needed.

Example: Unify data storage to UTC:

SET time_zone = '+00:00';

Q9: Can I handle dates and times separately in MySQL?

A9: Yes, you can extract the date part or time part using the DATE() function or TIME() function.

Example 1: Extracting only the date:

SELECT DATE(NOW());

Example 2: Extracting only the time:

SELECT TIME(NOW());

Q10: Can I use units not supported by MySQL’s INTERVAL clause?

A10: The units that can be used in MySQL’s INTERVAL clause are fixed. If you want to use other units, you need to convert them to appropriate units.

9. Summary

Date manipulation in MySQL is a crucial skill for database design and operations. Using functions like DATE_ADD, DATE_SUB, and the INTERVAL clause allows you to perform complex date calculations with ease.

Reviewing the Key Points of the Article

1. Basics of Date Types:

• In MySQL, you need to correctly distinguish between date types such as DATE, DATETIME, and TIMESTAMP.

1. DATE_ADD and DATE_SUB Functions:

• These are convenient functions for adding or subtracting dates, offering flexible operations with simple syntax.

1. INTERVAL Clause:

• This is an essential tool for efficient date calculations by specifying units such as years, months, days, and hours.

1. Practical Application Examples:

• It can be applied in various use cases, such as extracting past or future data, managing expiration dates, and setting reminders.

1. Notes and Best Practices:

• It’s important to understand key considerations for date operations, including data type consistency, timezone impacts, and end-of-month adjustments.

1. FAQ:

• We provided specific solutions to common questions and challenges faced by beginners.

Next Steps

• Applying What You’ve Learned in Practice:
• Try date operations using DATE_ADD or DATE_SUB in your own projects.
• Design Considering Timezones:
• For databases operating across multiple timezones, managing timezones and ensuring date calculation accuracy is crucial.
• Further Optimization:
• Implement efficient data operations by designing performance-oriented queries and utilizing indexes.

Mastering date manipulation in MySQL will dramatically improve the efficiency of data management and analysis. Use this article as a reference to actively apply this practical knowledge.