Sometimes you may need to get last 7 days records or get rows from last 7 days in MySQL. You can easily get records from last 7 days in MySQL, even if there is no function for it. Here’s the SQL query to select records for last 7 days.
How to Get Records from Last 7 Days in MySQL
Here’s the SQL to get records from last 7 days in MySQL. Let’s say you have the following table sales(order_date,sale) that contains daily sales data.
mysql> create table sales(order_date date,sale int);
mysql> insert into sales(order_date, sale)
values('2020-06-01',237),
('2020-06-02',230),
('2020-06-03',220),
('2020-06-04',210),
('2020-06-05',200),
('2020-06-06',260),
('2020-06-07',270),
('2020-06-08',240),
('2020-06-09',290),
('2020-06-10',230),
('2020-06-11',210);
mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-01 | 237 |
| 2020-06-02 | 230 |
| 2020-06-03 | 220 |
| 2020-06-04 | 210 |
| 2020-06-05 | 200 |
| 2020-06-06 | 260 |
| 2020-06-07 | 270 |
| 2020-06-08 | 240 |
| 2020-06-09 | 290 |
| 2020-06-10 | 230 |
| 2020-06-11 | 210 |
+------------+------+
Bonus Read : How to Get Records from Last 30 days
How to get records from last 7 days
Here’s the SQL query to get records from last 7 days in MySQL.
mysql> select * from sales
where order_date > now() - INTERVAL 7 day;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-05 | 200 |
| 2020-06-06 | 260 |
| 2020-06-07 | 270 |
| 2020-06-08 | 240 |
| 2020-06-09 | 290 |
| 2020-06-10 | 230 |
| 2020-06-11 | 210 |
+------------+------+
In the above query we select those records where order_date falls after a past interval of 7 days. We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 7 days in the past.
You can also use current_date instead of now()
mysql> select * from sales
where order_date > current_date - interval 7 day;
Bonus Read : How to Get Records Between 2 Dates in MySQL
How to Get Last 1 Week Data
Here’s how to get last 1 week record in MySQL
mysql> select * from sales
where order_date > now() - interval 1 week;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-05 | 200 |
| 2020-06-06 | 260 |
| 2020-06-07 | 270 |
| 2020-06-08 | 240 |
| 2020-06-09 | 290 |
| 2020-06-10 | 230 |
| 2020-06-11 | 210 |
+------------+------+
Bonus Read : How to Get Records from Last 24 Hours in MySQL
In the above query, we select rows where order_date falls after past 1 week interval. We use ‘1 week’ argument for INTERVAL clause, instead of using ‘7 day’.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.