The World of MySQL & MariaDB

MySQL BETWEEN for Date Ranges: Usage, Pitfalls & Performance

Operators and Clauses
目次

1. Introduction

The BETWEEN operator used in MySQL to specify date ranges is a handy feature that lets you retrieve data within a specific period with a simple query. For example, it’s useful for fetching sales data by month or searching for users whose registration date falls within a certain timeframe. However, when using BETWEEN, you need to be mindful of how you handle data types (DATE, DATETIME) and potential performance issues. This article will provide a detailed explanation from basic usage to advanced techniques.

2. Basics of MySQL’s BETWEEN Operator

2.1 Basic Syntax of BETWEEN

BETWEEN operator is used to retrieve values within a specified range. It has the following basic syntax.
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
This query retrieves data where order_date is from January 1, 2024 to January 31, 2024. The point is that BETWEEN includes both the start and end dates.

2.2 BETWEEN vs. Comparison Operators (>= AND <=)

You can achieve the same result by using comparison operators combined with >= and <=.
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date <= '2024-01-31';
BETWEEN Benefits:
  • Simple syntax with high readability
>= AND <= Benefits:
  • Allows finer control over range specification (e.g., when excluding time)
For example, using BETWEEN on a DATETIME column includes time information, which can retrieve unintended data. This point will be explained in detail in the next section.

3. Cautions When Using the BETWEEN Operator

3.1 Handling Columns Containing Time Information

BETWEEN used on a DATETIME column can produce unexpected results.
SELECT * FROM users
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
In this query, only data up to 2024-01-31 00:00:00 is retrieved, causing the issue that data from January 31 after midnight is excluded.

3.2 Correct Range Specification Method

To resolve this issue, the setting the end date to be less than the next day method is effective.
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
By using >= and < like this, you can reliably retrieve the full day’s data for January 31.

4. BETWEEN and Performance Optimization

4.1 Relationship between Indexes and BETWEEN

BETWEEN operator works quickly when indexes are set appropriately. However, using the DATE() function can cause the index to become ineffective, so caution is needed.
-- Index becomes ineffective (not recommended)
SELECT * FROM users
WHERE DATE(created_at) BETWEEN '2024-01-01' AND '2024-01-31';
Recommended query:</
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

4.2 Query Optimization Using EXPLAIN

To check performance, using the EXPLAIN command is convenient.
EXPLAIN SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
This allows you to verify the indexes used and the execution plan.

5. Common Mistakes and Their Solutions

5.1 Unintended Ranges Retrieved with BETWEEN

BETWEEN Even when you intend to use it, failing to consider time information can cause unintended data to be included or excluded. We recommend using a combination of >= and < as the correct approach.

5.2 Queries That Invalidate Indexes

DATE() function and CAST() conditions can invalidate indexes. Whenever possible, modify them to compare directly is the best approach.

6. Frequently Asked Questions (FAQ)

Q1: Does BETWEEN include the start date and end date?

→ Yes, BETWEEN includes both ends of the specified range.

Q2: Which should be used, BETWEEN or >= AND <=?

→ For simple range specifications, use BETWEEN, and when considering time information we recommend >= AND <.

Q3: Can using BETWEEN make a query slower?

→ Because using DATE() or CAST() makes indexes ineffective, we recommend using direct comparison.

7. Sample Queries for Practical Use

7.1 Retrieve Data for a Specific Year and Month

WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';

7.2 Retrieve Today’s Data

WHERE created_at BETWEEN CURDATE() AND CURDATE() + INTERVAL 1 DAY;

7.3 Retrieve Data for the Past 30 Days

WHERE created_at BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();

8. Summary

  • BETWEEN operator allows simple specification of date ranges, but you need to be careful when dealing with DATETIME type.
  • BETWEEN includes both the start and end dates, so you need to specify the range correctly.
  • Leveraging indexes is crucial for performance optimization, and you should avoid using the DATE() function.
  • Using >= AND < enables more reliable range specifications.
That concludes the key points for using the BETWEEN operator in MySQL. Please apply them in your work!