How to Get Last Week Data in MySQL

Sometimes you may need to fetch last week rows in MySQL for reporting and analysis. Here’s how to get last week data in MySQL.

How to Get Last Week Data in MySQL

Here are the steps to get last week data in MySQL. Let’s say you have the following table product_orders(id, order_date, amount)

mysql> create table product_orders(id int, order_date date, amount int);

mysql> insert into product_orders values(1,'2020-07-20',100),
     (2,'2020-07-21',250),
     (3,'2020-07-22',225),
     (4,'2020-07-23',150),
     (5,'2020-07-24',200),
     (6,'2020-07-25',180),
     (7,'2020-07-26',150),
     (8,'2020-07-27',200),
     (9,'2020-07-28',250),
     (10,'2020-07-29',300),
     (11,'2020-07-30',150),
     (12,'2020-07-31',200),
     (13,'2020-08-01',180),
     (14,'2020-08-02',200);

mysql> select * from product_orders;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-20 |    100 |
|    2 | 2020-07-21 |    250 |
|    3 | 2020-07-22 |    225 |
|    4 | 2020-07-23 |    150 |
|    5 | 2020-07-24 |    200 |
|    6 | 2020-07-25 |    180 |
|    7 | 2020-07-26 |    150 |
|    8 | 2020-07-27 |    200 |
|    9 | 2020-07-28 |    250 |
|   10 | 2020-07-29 |    300 |
|   11 | 2020-07-30 |    150 |
|   12 | 2020-07-31 |    200 |
|   13 | 2020-08-01 |    180 |
|   14 | 2020-08-02 |    200 |
+------+------------+--------+

Bonus Read : MySQL Copy Database

Here’s the SQL query to get last week’s data in MySQL.

mysql> select * from product_orders where week(order_date)=week(now())-1;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    7 | 2020-07-26 |    150 |
|    8 | 2020-07-27 |    200 |
|    9 | 2020-07-28 |    250 |
|   10 | 2020-07-29 |    300 |
|   11 | 2020-07-30 |    150 |
|   12 | 2020-07-31 |    200 |
|   13 | 2020-08-01 |    180 |
+------+------------+--------+

In the above SQL query, we use WEEK() function to get Week number of order_date column. We select only those records whose week number is 1 less than the week number of today’s date, obtained used NOW() function.

Bonus Read : MySQL Insert into Select

How to Get Last 7 Days Data in MySQL

Sometimes, you may need to fetch records of past 7 days. Here’s the SQL query to get last 7 days data in MySQL.

mysql> select * from product_orders where order_date> now() -  interval 7 day;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    9 | 2020-07-28 |    250 |
|   10 | 2020-07-29 |    300 |
|   11 | 2020-07-30 |    150 |
|   12 | 2020-07-31 |    200 |
|   13 | 2020-08-01 |    180 |
|   14 | 2020-08-02 |    200 |
+------+------------+--------+

In the above query, we select those records whose order_date is after a past interval of 7 days from today.

Bonus Read : MySQL Select Top N Rows

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.