MySQL Date Arithmetic: DATE_ADD, DATE_SUB, and INTERVAL (With Practical Examples)

1. Introduction

MySQL is an RDBMS (Relational Database Management System) widely used for web applications and database management. Among its many features, date operations are one of the most important functions developers use every day. For example, there are countless scenarios involving dates, such as setting recurring reminders, extracting data within a specific time range, and calculating schedules.

In particular, adding and subtracting dates is used frequently. MySQL provides convenient functions such as DATE_ADD and DATE_SUB, along with the INTERVAL clause for specifying durations. By leveraging these, you can keep code concise while efficiently performing complex date calculations.

In this article, we’ll explain date addition and subtraction in MySQL in a beginner-friendly way. We’ll cover everything from basic usage to advanced examples and provide practical know-how you can use in real projects. We’ll also explain common issues and important points to watch out for, so even if you’re not confident with date handling, you can learn comfortably.

By the time you finish reading, you’ll have the knowledge and practical techniques needed for MySQL date operations—and you’ll be able to apply them to your own projects.

2. Basic Knowledge You Need for MySQL Date Operations

When working with dates in MySQL, it’s important to understand date/time data types and fundamental concepts first. This section explains the basic knowledge you need for MySQL date operations.

Types of Date/Time Data

MySQL provides several data types for handling dates and times. Understanding each type’s characteristics and choosing appropriately is important.

1. DATE

• Stores a calendar date (year, month, day).
• Format: YYYY-MM-DD
• Example: 2025-01-01
• Use it when you only need a date (e.g., birthday, created date).

1. DATETIME

• Stores a date and time.
• Format: YYYY-MM-DD HH:MM:SS
• Example: 2025-01-01 12:30:45
• Use it when you need both date and time (e.g., an event timestamp).

1. TIMESTAMP

• Stores date/time based on UTC (Coordinated Universal Time) and supports 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

• Stores a time value.
• Format: HH:MM:SS
• Example: 12:30:45
• Use it when you need a pure time value (e.g., business hours).

1. YEAR

• Stores a year value only.
• Format: YYYY
• Example: 2025
• Use it when managing data at the year level.

Important Notes When Using Date/Time Types

• Timezone settings
  MySQL uses the server’s timezone settings by default. However, if your application uses a different timezone, data inconsistencies can occur. Set the timezone explicitly when needed.
• Handling invalid dates
  By default, MySQL is configured to throw an error if an invalid date (e.g., 2025-02-30) is specified. However, depending on server configuration, it may be converted to NULL or a default value, so it’s important to verify your settings.

Common Use Cases for Date Operations

1. Extracting data for a specific period

• Example: Retrieve sales data from the past week.

1. Schedule calculations

• Example: Send a notification 30 days after a user registers.

1. Managing expiration dates

• Example: Manage coupon expiration dates.

Summary of the Basics

MySQL provides powerful tools for handling dates and times. By understanding date/time data types and choosing them appropriately, you can improve database design and query efficiency. In the next section, we’ll take a closer look at the DATE_ADD function as a concrete method for date operations.

3. DATE_ADD Function: Basics and Practical Usage

The MySQL DATE_ADD function is a convenient tool used to add a specified interval to a date. This section explains everything from basic usage to practical examples.

What Is DATE_ADD?

DATE_ADD adds the duration specified in an INTERVAL clause to a given date and returns a new date. It’s a fundamental tool for simplifying date calculations.

Basic syntax:

DATE_ADD(date, INTERVAL value unit)

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

Available INTERVAL Units

You can use the following INTERVAL units with DATE_ADD.

Basic Examples

Here are concrete examples of DATE_ADD.

1. Calculate 1 day later:

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

Result: 2025-01-02

2. Calculate 1 month later:

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

Result: 2025-02-01

3. Calculate 1 year later:

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

Result: 2026-01-01

4. Calculate 3 hours later:

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

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

5. Add 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

You can also apply DATE_ADD to dynamically calculated dates.

1. Calculate 7 days from today:

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

Result: The date 7 days from today.

2. Calculate 30 minutes from now:

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

Result: The datetime 30 minutes from now.

Practical Examples

1. Calculating an expiration date:
   Calculate a 30-day warranty period from the purchase date.

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

2. Sending a reservation reminder:
   Calculate 3 days before an event date and send a reminder.

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

3. Calculating log retention cutoff:
   Calculate 90 days before today and delete logs older than that.

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

Notes

• Handling invalid dates:
  If the result of DATE_ADD becomes an invalid date, MySQL may return NULL. Make sure the input data is in a valid format.
• Timezone impact:
  If you’re using timezones, functions like CURRENT_TIMESTAMP and NOW() may be affected.

DATE_ADD is one of the most basic yet powerful tools for MySQL date operations. Once you master it, you’ll be able to handle many date calculations efficiently. In the next section, we’ll cover the DATE_SUB function in detail.

4. DATE_SUB Function: Basics and Practical Usage

DATE_SUB is the counterpart to DATE_ADD in MySQL. It’s used to subtract a specified interval from a date. This section explains the basics and practical examples you can use in real work.

What Is DATE_SUB?

DATE_SUB subtracts the duration specified in an INTERVAL clause from a given date or datetime and returns a new date. It’s a convenient tool for date subtraction.

Basic syntax:

DATE_SUB(date, INTERVAL value unit)

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

Basic Examples

Here are concrete examples of DATE_SUB.

1. Calculate 1 day earlier:

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

Result: 2024-12-31

2. Calculate 1 month earlier:

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

Result: 2024-12-01

3. Calculate 1 year earlier:

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

Result: 2024-01-01

4. Calculate 3 hours earlier:

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

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

5. Subtract 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

You can also use DATE_SUB based on dynamic dates.

1. Calculate 7 days before today:

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

Result: The date 7 days before today.

2. Calculate 30 minutes before now:

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

Result: The datetime 30 minutes before now.

Practical Examples

1. Retrieve data from the past 30 days:

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

2. Delete data older than 90 days:

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

3. Reminder notification:
   Set a reminder for 1 day before an event starts.

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

4. Shift calculation:
   Calculate 3 hours before a 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 becomes an invalid date, MySQL may return NULL. It’s important to validate the original date.
• Timezone impact:
  If the server timezone settings differ, results from DATE_SUB may shift unexpectedly. Use CONVERT_TZ() when needed.

DATE_SUB is just as important as DATE_ADD for MySQL date operations. By keeping subtraction logic concise, you can improve efficiency in data management and analysis. In the next section, we’ll explain 5. INTERVAL Clause Details and How to Use It.

5. INTERVAL Clause Details and How to Use It

The INTERVAL clause is used in MySQL date/time operations, typically together with DATE_ADD and DATE_SUB, to add or subtract a duration. This section explains the INTERVAL clause in detail, from the basics to advanced usage.

INTERVAL Clause Basics

The INTERVAL clause specifies how much time to add to or subtract from a target date. It’s often used with DATE_ADD and DATE_SUB, enabling powerful date operations with concise syntax.

Basic syntax:

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

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

Available Units

The following units can be used in the INTERVAL clause.

Basic 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 month-end:

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

Result: The 1st day 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 next quarter start date.

3. Timezone-aware time calculation
   Get the datetime one hour later calculated in the server’s default timezone.

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

4. Perform a more complex calculation
   Calculate the date two weeks from today and then get the last day of that month.

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

Notes

1. Be mindful of the result type
   The result of an INTERVAL calculation preserves the original type (DATE or DATETIME). Handle data types correctly.
2. Month-end behavior
   When adding/subtracting at month-end, MySQL may adjust to the end or start of the next month. For example, adding 1 month to 2025-01-31 results in 2025-02-28 (non-leap year).
3. Timezone impact
   If different timezones are configured on the server and client, datetime calculations can shift unexpectedly.

The INTERVAL clause is a key tool for simplifying MySQL date operations. Thanks to its flexibility, you can handle complex date calculations with ease. In the next section, we’ll explain 6. Practical Examples.

6. Practical Examples

Using MySQL’s DATE_ADD function and the INTERVAL clause makes complex date calculations and dynamic processing straightforward. This section introduces several practical examples that are useful in real work.

1. Extracting Data Within a Specific Period

In data analysis and reporting, it’s common to extract records within a specific time range.

Example 1: Retrieve sales data from the past 30 days

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

Explanation: Extracts data on or after the date that is 30 days before today.

Example 2: Retrieve data for the previous month

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

Explanation: Retrieves order data for the previous month.

2. Setting Event Reminders

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

Example: Send a notification 3 days before an event starts

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

Explanation: Calculates the date 3 days before the event date and sets it as the notification date.

3. Managing Expiration Dates

Calculating expiration dates based on a registration date is used in many applications.

Example 1: Calculate an expiration date 30 days after registration

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

Explanation: Calculates an expiration date 30 days after the registration date.

Example 2: Delete expired data

DELETE FROM sessions
WHERE expiry_date < NOW();

Explanation: Deletes session data that has expired based on the current datetime.

4. Shift and Schedule Calculations

Examples of adjusting times based on start or end times for work schedules.

Example: Calculate 1 hour before a shift starts

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

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

5. Calculations Combining Multiple INTERVALs

Examples of combining multiple durations in calculations.

Example 1: Get the last day of the month one month from today

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

Explanation: Calculates the last day of the month one month after today.

Example 2: Add a 3-month grace period after 1 year from registration

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

Explanation: Calculates a final deadline by adding 1 year and then an additional 3 months from the registration date.

6. Data Cleaning and Optimization

Examples of optimizing a database by deleting old data.

Example: Delete users who haven’t logged in for the past 90 days

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

Explanation: Deletes users whose last login is older than 90 days from today.

Notes

• Validate calculation results: Pay special attention to boundary cases such as month-end and leap years.
• Ensure data type consistency: Specifying correct date/time types (DATE, DATETIME, etc.) helps prevent errors.
• Consider timezones: When operating across different timezones, use the CONVERT_TZ() function.

By applying DATE_ADD and the INTERVAL clause, you can efficiently streamline date operations in real work. In the next section, we’ll explain 7. Notes and Best Practices in detail.

7. Notes and Best Practices

Date operations in MySQL are extremely useful, but when using them in real work you need to understand several key points and best practices. This section explains what to watch out for and how to use date operations efficiently.

Notes

1. Data Type Consistency

• Issue: In MySQL, results can differ depending on the data type used for date/time operations (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 an invalid date

• Countermeasure: Validate that input data is in the correct format beforehand, and choose appropriate data types.

2. Handling Invalid Dates

• Issue: Calculations involving month-end or leap years may produce tricky edge cases.
• Example:

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

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

• Countermeasure: When handling month-end dates, verify using the LAST_DAY() function.

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

3. Timezone Considerations

• Issue: If the server timezone differs from the application timezone, data inconsistencies or unexpected results can occur.
• Example:

  SELECT NOW(); -- Depends on the server timezone

• Countermeasure: Set the timezone explicitly.

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

4. Performance in Complex Calculations

• Issue: Queries with complex date calculations can impact execution speed.
• Countermeasure: Reduce unnecessary calculations and optimize queries using indexes.

Best Practices

1. Use Standard Formats

• Keep date formats consistent.
• Recommended formats: YYYY-MM-DD (DATE), YYYY-MM-DD HH:MM:SS (DATETIME).
• Example:

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

2. Combine Functions Effectively

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

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

3. Plan Dynamic Date Operations Carefully

• When working with dynamic dates, split queries and process them step by step.
• Example:

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

4. Manage Timezones Safely

• Keep timezones consistent between server and client.
• Example:

  SET time_zone = '+09:00';

5. Validate in a Test Environment

• Test complex date calculations and calculations across different timezones in advance.

Avoid Common Pitfalls in Date Operations

Date operations are an important skill that can significantly affect database design and query accuracy. By understanding the pitfalls and following best practices, you can prevent issues and achieve efficient data handling.

8. FAQ

Here are frequently asked questions (FAQ) about MySQL date operations. These answers focus on common points that beginners to intermediate users often wonder about.

Q1: What’s the difference between DATE_ADD and using the + operator with INTERVAL?

A1:

• DATE_ADD() is a dedicated function for date calculations and is better for error handling and type conversion.
• + INTERVAL is simpler, but type conversion errors can occur in some cases.

Example: Using DATE_ADD:

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

Example: Using + operator with INTERVAL:

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

In general, using DATE_ADD() is recommended.

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

A2:
No. DATE_ADD() can only specify one interval at a time. However, you can nest multiple DATE_ADD() calls to achieve the same effect.

Example: Add 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:
Use the DATE_FORMAT() function to format a date/time as you like.

Example: Format conversion:

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

Result: 2025-01-01 12:30:45

Q4: What happens if I add 1 month to a month-end date?

A4:
MySQL adjusts automatically, and the result becomes the last day of the next month.

Example:

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

Result: 2025-02-28

This behavior is convenient, but you should confirm whether it matches your intended logic.

Q5: How do I do timezone-aware date calculations?

A5:
Use MySQL’s CONVERT_TZ() function to specify the timezone.

Example: Convert 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 in DATE_ADD?

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

Validating inputs beforehand is recommended.

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

A7:
Use DATE_ADD or DATE_SUB to compute a reference date and then use that result in your WHERE clause.

Example: Retrieve data from the past 30 days:

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

Q8: What are best practices when using multiple timezones?

A8:

• Store all datetime data in UTC.
• Convert to the required timezone on the client side as needed.

Example: Save data in UTC:

SET time_zone = '+00:00';

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

A9:
Yes. Use DATE() or TIME() to extract the date or time portion.

Example 1: Extract date only:

SELECT DATE(NOW());

Example 2: Extract time only:

SELECT TIME(NOW());

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

A10:
MySQL supports a fixed set of INTERVAL units. If you need something else, convert it to an appropriate supported unit.

9. Summary

Date operations in MySQL are a crucial skill for database design and operation. By using DATE_ADD, DATE_SUB, and the INTERVAL clause, you can perform complex date calculations easily and efficiently.

Key Takeaways

1. Basics of date/time types:

• In MySQL, you need to use date/time types such as DATE, DATETIME, and TIMESTAMP correctly.

1. DATE_ADD and DATE_SUB:

• Convenient functions for adding or subtracting dates, with flexible operations using simple syntax.

1. INTERVAL clause:

• An essential tool for efficient date calculations using units like years, months, days, and hours.

1. Practical examples:

• Useful for many use cases such as extracting past/future data, managing expiration dates, and setting reminders.

1. Notes and best practices:

• It’s important to understand considerations like data type consistency, timezone impact, and month-end adjustments.

1. FAQ:

• Provided concrete solutions to common questions and issues beginners often face.

Next Steps

• Apply what you learned:
• Try using DATE_ADD and DATE_SUB in your own project to practice date operations.
• Design with timezones in mind:
• In databases operating across multiple timezones, accurate timezone management and date calculations are critical.
• Further optimization:
• Design efficient queries and use indexes with performance in mind for better data operations.

Mastering MySQL date operations will greatly improve the efficiency of managing and analyzing data. Use this article as a reference and actively apply these practical skills in your work.