MySQL: Select Rows Where Date is Within Last 30 Days


You can use the following basic syntax in MySQL to return all rows in a table where the date in a date column is within the last 30 days:

SELECT *
FROM sales
WHERE sales_date > NOW() - INTERVAL 30 DAY;

This particular example selects all rows in the table named sales where the date in the sales_date column is within the last 30 days.

Note: The NOW() function in MySQL returns the current date and time.

The following example shows how to use this syntax in practice.

Example: How to Select Rows where Date is Within Last 30 Days in MySQL

Suppose we have the following table named sales that contains information about sales made at various grocery stores at various times:

-- create table 
CREATE TABLE sales (
  store_ID INT PRIMARY KEY,
  item TEXT NOT NULL,
  sales_date DATETIME NOT NULL
);

-- insert rows into table
INSERT INTO sales VALUES (0001, 'Oranges', '2024-02-10 03:45:00');
INSERT INTO sales VALUES (0002, 'Apples', '2020-11-25 15:25:01');
INSERT INTO sales VALUES (0003, 'Bananas', '2009-06-30 09:01:39');
INSERT INTO sales VALUES (0004, 'Melons', '2024-01-14 03:29:55');
INSERT INTO sales VALUES (0005, 'Grapes', '2023-05-19 23:10:04');

-- view all rows in table
SELECT * FROM sales;

Output:

+----------+---------+---------------------+
| store_ID | item    | sales_date          |
+----------+---------+---------------------+
|        1 | Oranges | 2024-02-10 03:45:00 |
|        2 | Apples  | 2020-11-25 15:25:01 |
|        3 | Bananas | 2009-06-30 09:01:39 |
|        4 | Melons  | 2024-01-14 03:29:55 |
|        5 | Grapes  | 2023-05-19 23:10:04 |
+----------+---------+---------------------+

This article is currently being written on 2/12/2024.

Suppose that we would like to select all rows where the date in the sales_date column is within the last 30 days.

We can use the following syntax to do so:

SELECT *
FROM sales
WHERE sales_date > NOW() - INTERVAL 30 DAY;

Output:

+----------+---------+---------------------+
| store_ID | item    | sales_date          |
+----------+---------+---------------------+
|        1 | Oranges | 2024-02-10 03:45:00 |
|        4 | Melons  | 2024-01-14 03:29:55 |
+----------+---------+---------------------+

Notice that each of the rows in the resulting table have a date in the sales_date column that is within 30 days of today’s date of 2/12/2024.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Return All Rows Between Two Dates
MySQL: How to Return All Rows Greater Than Date
MySQL: How to Select Rows where Date is Equal to Today

Leave a Reply

Your email address will not be published. Required fields are marked *