How can we select rows in MySQL where the date is older than 7 days ago?

---

You can use the following basic syntax in MySQL to return all rows in a table where the date in a date column is older than 7 days ago:

SELECT *
FROM sales
WHERE sales_date < NOW() - INTERVAL 1 WEEK;

This particular example selects all rows in the table named sales where the date in the sales_date column is older than 7 days ago.

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

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

Example: Select Rows where Date is Older Than 7 Days Ago 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 older than 7 days ago.

We can use the following syntax to do so:

SELECT *
FROM sales
WHERE sales_date < NOW() - INTERVAL 1 WEEK;

Output:

+----------+---------+---------------------+
| store_ID | item    | sales_date          |
+----------+---------+---------------------+
|        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 |
+----------+---------+---------------------+

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

Additional Resources

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

MySQL: How to Select Rows where Date is Equal to Today