Sometimes you may need to get current week records or fetch this week’s records for reporting and analysis. In this article, we will look at how to get current week data in MySQL.
How to Get Current Week Data in MySQL
Let us say you have the following table sales(id, order_date, amount)
mysql> create table sales(id int, order_date date, amount int);
mysql> insert into sales(id, order_date, amount)
values(1, '2021-01-24',250),
(2, '2021-01-25',250),
(3, '2021-01-26',250),
(4, '2021-01-27',250),
(5, '2021-01-28',250),
(6, '2021-01-29',250),
(7, '2021-01-30',250),
(8, '2021-01-31',250),
(9, '2021-02-01',250);
mysql> select * from sales;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 1 | 2021-01-24 | 250 |
| 2 | 2021-01-25 | 250 |
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
| 5 | 2021-01-28 | 250 |
| 6 | 2021-01-29 | 250 |
| 7 | 2021-01-30 | 250 |
| 8 | 2021-01-31 | 250 |
| 9 | 2021-02-01 | 250 |
+------+------------+--------+
Also read : How to Select top 10 records of each category in MySQL
How to Get Records of current week in MySQL
It is very easy to get current week data in MySQL. Here is the SQL query to get records of current week in MySQL.
mysql> select *
from sales
where week(order_date)=week(now());
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 8 | 2021-01-31 | 250 |
| 9 | 2021-02-01 | 250 |
+------+------------+--------+
In the above query, we use now() function to get present date, and week() function to get week number of date values. So we select rows whose order_date’s week number is same as week number of today’s day.
Also read : Common Table Expression in MySQL
How to get last 7 days data in MySQL
Sometimes you may also need to get last 7 days data in MySQL. Here is the SQL query to get last 7 days data.
mysql> select *
from sales
where order_date> now() - interval 1 week;
+------+------------+--------+
| id | order_date | amount |
+------+------------+--------+
| 3 | 2021-01-26 | 250 |
| 4 | 2021-01-27 | 250 |
| 5 | 2021-01-28 | 250 |
| 6 | 2021-01-29 | 250 |
| 7 | 2021-01-30 | 250 |
| 8 | 2021-01-31 | 250 |
| 9 | 2021-02-01 | 250 |
+------+------------+--------+
In the above SQL query, we select rows whose order_date falls after past 7 days. We use INTERVAL keyword to subtract 1 week from present date.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
