How to Get Records from Last 7 Days in MySQL

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.

Table of Contents

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!

MySQL Add Unique Constraint

How to Get Record with Max Value in MySQL

Select Top 10 Records for Each Category in MySQL