MySQL Date Comparison and Manipulation: Complete Guide with Examples & Performance Tips

1. Introduction

Handling dates in MySQL is one of the most important aspects of database operations. For example, when aggregating sales data by date or searching records within a specific period, date comparison becomes essential.

This article explains everything from the basics of date manipulation and comparison in MySQL to advanced use cases and performance optimization techniques. It is designed to be useful for users at all levels, from beginners to intermediate developers.

2. Overview of MySQL Date Data Types

Types of Date Data Types and Their Characteristics

MySQL provides the following three main date data types. Below is a brief explanation of each.

1. DATE

• Stored Value: Year-Month-Day (YYYY-MM-DD)
• Characteristics: Does not include time information, making it suitable for managing simple dates.
• Use Cases: Birthdays, deadlines.

1. DATETIME

• Stored Value: Year-Month-Day and Time (YYYY-MM-DD HH:MM:SS)
• Characteristics: Includes time information, making it suitable for recording precise timestamps.
• Use Cases: Creation timestamps, last updated timestamps.

1. TIMESTAMP

• Stored Value: Year-Month-Day and Time (YYYY-MM-DD HH:MM:SS)
• Characteristics: Time zone dependent, making it useful for managing date and time across different regions.
• Use Cases: Log data, transaction records.

How to Choose the Appropriate Data Type

• If time is not required, choose DATE.
• If time is required, choose DATETIME or TIMESTAMP depending on your application’s time zone requirements.

Sample Query

Below is an example using each data type.

CREATE TABLE events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE,
    event_datetime DATETIME,
    event_timestamp TIMESTAMP
);

3. How to Compare Dates

Using Basic Comparison Operators

In MySQL, the following operators are used for date comparison.

1. = (Equal)

• Retrieves records that match the specified date.

SELECT * FROM events WHERE event_date = '2025-01-01';

2. > / < (Greater Than / Less Than)

• Searches for records before or after the specified date.

SELECT * FROM events WHERE event_date > '2025-01-01';

3. <= / >= (Less Than or Equal / Greater Than or Equal)

• Searches within a range that includes the specified date.

SELECT * FROM events WHERE event_date <= '2025-01-10';

Range Queries Using BETWEEN

The BETWEEN operator allows you to easily specify a date range.

SELECT * FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';

Important Note:

• BETWEEN includes both the start and end values, so make sure no required data is unintentionally excluded from your range.

4. Calculating Date Differences

Using the DATEDIFF Function

The DATEDIFF() function calculates the difference (in days) between two dates.

SELECT DATEDIFF('2025-01-10', '2025-01-01') AS days_difference;

Result:

• 9 days

Using the TIMESTAMPDIFF Function

The TIMESTAMPDIFF() function allows you to calculate the difference in specific units such as years, months, days, or hours.

SELECT TIMESTAMPDIFF(MONTH, '2025-01-01', '2025-12-31') AS months_difference;

Result:

• 11 months

5. Date Addition and Subtraction

Date Manipulation Using the INTERVAL Clause

In MySQL, you can easily add or subtract time from a date using the INTERVAL clause. This allows you to create queries that retrieve dates before or after a specific period.

Adding to a Date

Example of adding time to a date using INTERVAL:

SELECT DATE_ADD('2025-01-01', INTERVAL 7 DAY) AS plus_seven_days;

Result:
2025-01-08

Subtracting from a Date

You can subtract time from a date in a similar way using INTERVAL:

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

Result:
2024-12-01

Use Case: Reminder System

Below is an example query that retrieves events occurring exactly seven days ago, which can be used for sending automated reminder notifications.

SELECT event_name, event_date 
FROM events 
WHERE event_date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

Calculations Based on the Current Date

• CURDATE(): Returns the current date (YYYY-MM-DD).
• NOW(): Returns the current date and time (YYYY-MM-DD HH:MM:SS).

Example: Calculate the date one week from today.

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

6. Practical Query Examples

Retrieve Records for a Specific Date

Retrieve events that match the specified date.

SELECT * 
FROM events 
WHERE event_date = '2025-01-01';

Retrieve Data Within a Date Range

Example of searching for events within a one-week range.

SELECT * 
FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-07';

Aggregating Data by Date

This query counts how many events are registered for each month.

SELECT MONTH(event_date) AS event_month, COUNT(*) AS total_events 
FROM events 
GROUP BY MONTH(event_date);

Example Result:

7. Performance Optimization

Efficient Searching Using Indexes

Setting an index on a date column can significantly improve query performance.

Creating an Index

The following SQL creates an index on the event_date column.

CREATE INDEX idx_event_date ON events(event_date);

Checking the Effect of an Index

You can use EXPLAIN to check the query execution plan.

EXPLAIN SELECT * 
FROM events 
WHERE event_date = '2025-01-01';

Important Notes When Using Functions

Using functions in the WHERE clause may disable index usage.

Bad Example

In the following query, the DATE() function prevents the index from being used.

SELECT * 
FROM events 
WHERE DATE(event_date) = '2025-01-01';

Improved Example

It is recommended to compare values directly without using functions.

SELECT * 
FROM events 
WHERE event_date >= '2025-01-01' 
  AND event_date < '2025-01-02';

8. FAQ (Frequently Asked Questions)

Q1: What is the difference between DATE and DATETIME?

• A1:
• DATE: Stores only the date (YYYY-MM-DD). Use it when time information is not required.
• DATETIME: Stores both date and time (YYYY-MM-DD HH:MM:SS). Use it when the exact timestamp of an event is required.

Example:

CREATE TABLE examples (
    example_date DATE,
    example_datetime DATETIME
);

Q2: What should I be aware of when specifying a date range using BETWEEN?

• A2:
• Since BETWEEN includes both the start and end dates, if the end date does not include a time value, you may not retrieve all intended records.

Example:

-- This query may not retrieve records such as "2025-01-31 23:59:59"
SELECT * 
FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';

Improvement:
Explicitly set the end time to 23:59:59 or use a comparison that ignores the time portion.

SELECT * 
FROM events 
WHERE event_date >= '2025-01-01' AND event_date < '2025-02-01';

Q3: How should time zone differences be handled?

• A3:
  In MySQL, the TIMESTAMP type is time zone dependent. In contrast, the DATETIME type stores a fixed value and is not affected by time zones.

Check current time zone setting:

SHOW VARIABLES LIKE 'time_zone';

Change time zone setting:

SET time_zone = '+09:00'; -- Japan Standard Time (JST)

Q4: How can I retrieve data from the past 30 days?

• A4:
  You can combine CURDATE() or NOW() with INTERVAL to retrieve data from the past 30 days.

Example:

SELECT * 
FROM events 
WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Q5: How can I improve the performance of date comparisons?

• A5:
• Create indexes: Set an index on date columns.
• Avoid using functions: Using functions in the WHERE clause may disable indexes, so use direct comparisons instead.

9. Summary

Key Points of This Article

This article provided a comprehensive explanation of date manipulation and comparison techniques in MySQL. The key takeaways are as follows:

1. Understanding the characteristics and proper usage of date data types (DATE, DATETIME, TIMESTAMP).
2. Basic comparison methods (=, >, <, BETWEEN, etc.).
3. Calculating date differences (DATEDIFF(), TIMESTAMPDIFF()).
4. Improving performance through indexing and optimal query design.

We hope this guide helps you efficiently handle date operations in MySQL and develop practical, optimized queries for real-world applications.