MySQL Date Comparison: Best Queries & Performance Tips

1. Introduction

2. Overview of MySQL Date Data Types

Types and Characteristics of Date Data Types

1. DATE

• Stored value: year, month, and day (YYYY-MM-DD)
• Feature: Since it does not include time information, it is suitable for managing simple dates.
• Examples: birthdays, deadlines.

1. DATETIME

• Stored value: year, month, day, and time (YYYY-MM-DD HH:MM:SS)
• Feature: Because it includes time information, it is suitable for recording precise timestamps.
• Examples: creation timestamp, last update timestamp.

1. TIMESTAMP

• Stored value: year, month, day, and time (YYYY-MM-DD HH:MM:SS)
• Feature: Because it is timezone-dependent, it is convenient for managing dates and times across different regions.
• Examples: log data, transaction records.

Guidelines for Choosing the Appropriate Data Type

• If you don’t need time, choose DATE.
• If you need time, select DATETIME or TIMESTAMP based on your application’s timezone requirements.

Sample Query

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

3. How to Compare Dates

How to Use Basic Comparison Operators

1. = (equal)

• Retrieves data that matches the specified date.

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

2. > / < (greater than / less than)

• Searches for data 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 for a range that includes the specified date.

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

Range Search Using BETWEEN

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

• BETWEEN includes both the start and end values of the range, so make sure no data within the range is missed.

4. How to Calculate Date Differences

How to Use the DATEDIFF Function

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

• 9 days

Using the TIMESTAMPDIFF Function

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

• 11 months

5. Date Addition and Subtraction

Date Manipulation Using the INTERVAL Clause

Date Addition

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

Date Subtraction

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

Example Use: Reminder System

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).

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

6. Practical Query Examples

Retrieve records for a specific date

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

Extract data within a date range

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

Aggregation based on dates

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

7. Performance Optimization

Efficient Searching Using Indexes

Creating an Index

CREATE INDEX idx_event_date ON events(event_date);

Verifying the Effect of the Index

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

Cautions When Using Functions

Bad Example

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

Improved Example

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 types?

• A1:
• DATE type: Stores only the date (YYYY-MM-DD). Use when time information is not needed.
• DATETIME type: Stores date and time (YYYY-MM-DD HH:MM:SS). Use when the exact timing of events is required.

CREATE TABLE examples (
    example_date DATE,
    example_datetime DATETIME
);

Q2: What should you watch out for when specifying a date range with BETWEEN?

• A2:
• BETWEEN includes both the start and end dates, so if the end date has no time set, you may not retrieve the intended data.

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

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

Q3: How do you handle time zone differences?

• A3: MySQL’s TIMESTAMP type is time zone–aware. In contrast, the DATETIME type is stored as a fixed value and is not affected by time zones.

SHOW VARIABLES LIKE 'time_zone';

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

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

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

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

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

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

9. Summary

Key Points

1. Characteristics and appropriate use of date data types (DATE, DATETIME, TIMESTAMP).
2. Basic comparison operators (=, >, <, BETWEEN, etc.).
3. Methods for calculating date differences (DATEDIFF(), TIMESTAMPDIFF()).
4. Using indexes and optimal query design to improve performance.