MySQL Filter by Date: Today, This Month, Last 7 Days

Date filters are everywhere: “orders today”, “signups this month”, “events in the next 7 days”. Getting them wrong gives missing rows or slow queries.

query.sql

SQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer VARCHAR(20) NOT NULL,
    total DECIMAL(8,2) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_created (created_at)
);
INSERT INTO orders (customer, total, created_at) VALUES
    ('Maya',  680.00, '2026-06-28 09:15:00'),
    ('Rohan', 145.00, '2026-06-30 23:59:59'),
    ('Sana',  450.00, '2026-07-01 00:00:00'),
    ('Maya',  999.00, '2026-07-01 17:40:00'),
    ('Tara',   80.00, '2026-07-02 08:05:00'),
    ('Uday',  220.00, '2026-07-15 12:00:00');

One day

The wrong way works, but is slow on large tables:

query.sql

SQL

SELECT id, customer, created_at FROM orders WHERE DATE(created_at) = '2026-07-01';

Output

OUTPUT

+----+----------+---------------------+
| id | customer | created_at          |
+----+----------+---------------------+
|  3 | Sana     | 2026-07-01 00:00:00 |
|  4 | Maya     | 2026-07-01 17:40:00 |
+----+----------+---------------------+
2 rows in set

The right way is a half-open range:

query.sql

SQL

SELECT id, customer, created_at FROM orders WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02';

Output

OUTPUT

+----+----------+---------------------+
| id | customer | created_at          |
+----+----------+---------------------+
|  3 | Sana     | 2026-07-01 00:00:00 |
|  4 | Maya     | 2026-07-01 17:40:00 |
+----+----------+---------------------+
2 rows in set

Same rows. But the first query applies DATE() to every row, so MySQL cannot use idx_created. The second compares the column directly and uses the index. Look at the plans:

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM orders WHERE DATE(created_at) = '2026-07-01';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM orders WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02';

Output

OUTPUT

+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | index | NULL          | idx_created | 5       | NULL |    6 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set

+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | range | idx_created   | idx_created | 5       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set

On six rows the difference is invisible. On six million, the first reads them all, and the second reads only that day.

One month

query.sql

SQL

1
2
3

SELECT id, customer, created_at FROM orders
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01'
ORDER BY created_at;

Output

OUTPUT

+----+----------+---------------------+
| id | customer | created_at          |
+----+----------+---------------------+
|  3 | Sana     | 2026-07-01 00:00:00 |
|  4 | Maya     | 2026-07-01 17:40:00 |
|  5 | Tara     | 2026-07-02 08:05:00 |
|  6 | Uday     | 2026-07-15 12:00:00 |
+----+----------+---------------------+
4 rows in set

The same pattern, always: start included, next start excluded. It handles the last second of the month, leap years, different month lengths and fractional seconds with no extra thought.

Relative dates: last 7 days, this month

Use CURRENT_DATE, NOW() and INTERVAL. To keep this lesson repeatable, we write them with a fixed “today” of 2026-07-02:

query.sql

SQL

SET @today = '2026-07-02';

SELECT id, customer, created_at FROM orders
WHERE created_at >= @today - INTERVAL 7 DAY AND created_at < @today + INTERVAL 1 DAY
ORDER BY created_at;

Output

OUTPUT

+----+----------+---------------------+
| id | customer | created_at          |
+----+----------+---------------------+
|  1 | Maya     | 2026-06-28 09:15:00 |
|  2 | Rohan    | 2026-06-30 23:59:59 |
|  3 | Sana     | 2026-07-01 00:00:00 |
|  4 | Maya     | 2026-07-01 17:40:00 |
|  5 | Tara     | 2026-07-02 08:05:00 |
+----+----------+---------------------+
5 rows in set

In real code, replace @today with CURRENT_DATE:

Need	Condition
Today	`created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + INTERVAL 1 DAY`
Yesterday	`created_at >= CURRENT_DATE - INTERVAL 1 DAY AND created_at < CURRENT_DATE`
Last 7 days (including today)	`created_at >= CURRENT_DATE - INTERVAL 6 DAY`
This month	`created_at >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AND created_at < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') + INTERVAL 1 MONTH`
Last 30 days	`created_at >= NOW() - INTERVAL 30 DAY`

query.sql

SQL

1
2
3

SELECT CURRENT_DATE - INTERVAL 1 DAY < CURRENT_DATE AS yesterday_is_earlier,
       DATE_FORMAT('2026-07-15', '%Y-%m-01') AS first_of_month,
       DATE_FORMAT('2026-07-15', '%Y-%m-01') + INTERVAL 1 MONTH AS first_of_next_month;

Output

OUTPUT

+----------------------+----------------+---------------------+
| yesterday_is_earlier | first_of_month | first_of_next_month |
+----------------------+----------------+---------------------+
|                    1 | 2026-07-01     | 2026-08-01          |
+----------------------+----------------+---------------------+
1 row in set

Time zones

NOW() and CURRENT_DATE use the session time zone. If your server runs in UTC and your users in India, “today” differs by hours. Decide which zone defines “today” and set it (DATETIME vs TIMESTAMP).

Year and month filters

YEAR(created_at) = 2026 and MONTH(created_at) = 7 are easy to read, but they cannot use an index (the function is applied to the column). Turn them into ranges:

query.sql

SQL

SELECT COUNT(*) AS orders_in_2026 FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Output

OUTPUT

+----------------+
| orders_in_2026 |
+----------------+
|              6 |
+----------------+
1 row in set

If you must filter by the month number regardless of the year, add a generated column with an index (Generated Columns).

Age and birthdays

query.sql

SQL

1
2

SELECT TIMESTAMPDIFF(YEAR, '1990-08-15', '2026-07-02') AS age_in_years,
       DATEDIFF('2026-12-25', '2026-07-02')               AS days_until;

Output

OUTPUT

+--------------+------------+
| age_in_years | days_until |
+--------------+------------+
|           35 |        176 |
+--------------+------------+
1 row in set

lightbulb

One rule for all date filters

Keep the column bare on the left side. Calculate the boundaries on the right side. column >= start AND column < end.