How to Get Current Week Data in MySQL

Last updated on June 18th, 2024 at 06:33 am

MySQL database provides many useful functions and operators to do time series analysis. Often database programmers and data analysts need to get current week records or fetch this week’s records for reporting and analysis. This is commonly required if you are running Ecommerce sites and need to generate weekly sales reports. In this article, we will look at how to get current week data in MySQL.

Table of Contents

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 |
 +------+------------+--------+

There are 4 commonly used solutions to get current week’s data in MySQL.

1. Using Week() Function

Week() function returns the week number (0-53) of a given date string. The date value can be input as literal string or column name or result of a function. 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.

2. Using Yearweek() Function

The Yearweek() function returns the year and week number of a given date string, which can be provided as literal string or column name or function. Here is the SQL query to get current week’s sales data using this function.

mysql> select * 
      from sales 
      where yearweek(order_date)=yearweek(now());
 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-01 |    250 |
 +------+------------+--------+

This query is similar to the one we used above with week() function. In this case, we simply use yearweek() instead of week() function. In this query, we select rows where the yearweek() value of order_date column is same as that of the present date, returned using now() function.

3. Using Date_format() Function

Date_format() is a versatile and powerful MySQL function that allows you to format date, time, datetime values in almost any way you want, thanks to the vast number of format strings it supports. Here is a simple query to get current week’s data using date_format() function.

mysql> select * 
      from sales 
      where date_format(order_date, '%U')=date_format(now(), '%U');
 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-01 |    250 |
 +------+------------+--------+

In the above query, we use date_format() with ‘%U’ format specifier to get the week number of date. We select rows where the week number of order_date is same as that of present date. Here is a list of format specifiers supported by date_format() function.

4. Using Interval

This is a variation of our original use case. Sometimes you may also need to get last 7 days data in MySQL, instead of current week’s data. You can easily do this by using INTERVAL clause, that allows you to subtract or add a specific time interval to a given date. 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.

Conclusion

In this article, we have learnt 4 different ways to retrieve data for present week in MySQL. You can use it to generate weekly reports about sign ups, sales, orders, transactions and more. The basic idea in each of the solution is to get the rows where week number of date column of your table is same as that of week number of present date. We have also learnt how to get date for past 7 days, instead of current week. You can use any of the above methods as per your requirement.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read
How to Select top 10 records of each category in MySQL
Common Table Expression in MySQL
How to Calculate Margin in MySQL
MySQL Query to Get Top Selling Products