Sometimes you may need to get last 15 days rows in MySQL. Here’s how to get last 15 days records in MySQL. You can also use it to get number of users signed up in last 15 days, or select last 15 days sales data for further analysis.
How to Get Last 15 Days Records in MySQL
Here are the steps to get last 15 days records in MySQL.
Let’s say you have the following table sales(order_date, sale, orders) that contains daily number of orders and sale amount.
mysql> create table sales(order_date date,sale int, orders int);
mysql> insert into sales(order_date ,sale ,orders )
values( '2020-04-28' , 300 , 10 ),
( '2020-04-29' , 250 , 15 ),
( '2020-04-30' , 250 , 12 ),
( '2020-05-01' , 250 , 14 ),
( '2020-05-02' , 150 , 20 ),
( '2020-05-03' , 300 , 21 ),
( '2020-05-04' , 200 , 15 ),
( '2020-05-05' , 200 , 17 ),
( '2020-05-06' , 250 , 12 ),
( '2020-05-07' , 150 , 15 ),
( '2020-05-08' , 300 , 12 ),
( '2020-05-09' , 280 , 18 ),
( '2020-05-10' , 320 , 16 ),
( '2020-05-11' , 400 , 15 ),
( '2020-05-12' , 250 , 13 ),
( '2020-05-13' , 100 , 16 ),
( '2020-05-14' , 200 , 18 );
mysql> select * from sales;
+------------+------+--------+
| order_date | sale | orders |
+------------+------+--------+
| 2020-04-28 | 300 | 10 |
| 2020-04-29 | 250 | 15 |
| 2020-04-30 | 250 | 12 |
| 2020-05-01 | 250 | 14 |
| 2020-05-02 | 150 | 20 |
| 2020-05-03 | 300 | 21 |
| 2020-05-04 | 200 | 15 |
| 2020-05-05 | 200 | 17 |
| 2020-05-06 | 250 | 12 |
| 2020-05-07 | 150 | 15 |
| 2020-05-08 | 300 | 12 |
| 2020-05-09 | 280 | 18 |
| 2020-05-10 | 320 | 16 |
| 2020-05-11 | 400 | 15 |
| 2020-05-12 | 250 | 13 |
| 2020-05-13 | 100 | 16 |
| 2020-05-14 | 200 | 18 |
+------------+------+--------+
Bonus Read : How to Get Last Record in Each Group
How to Get Last 15 Days Sales Data in SQL
Here’s the SQL query to get last 15 days records in MySQL
mysql> select * from sales
where order_date> now() - INTERVAL 15 day;
+------------+------+--------+
| order_date | sale | orders |
+------------+------+--------+
| 2020-04-30 | 250 | 12 |
| 2020-05-01 | 250 | 14 |
| 2020-05-02 | 150 | 20 |
| 2020-05-03 | 300 | 21 |
| 2020-05-04 | 200 | 15 |
| 2020-05-05 | 200 | 17 |
| 2020-05-06 | 250 | 12 |
| 2020-05-07 | 150 | 15 |
| 2020-05-08 | 300 | 12 |
| 2020-05-09 | 280 | 18 |
| 2020-05-10 | 320 | 16 |
| 2020-05-11 | 400 | 15 |
| 2020-05-12 | 250 | 13 |
| 2020-05-13 | 100 | 16 |
| 2020-05-14 | 200 | 18 |
+------------+------+--------+
In the above query, we use system function now() to get current datetime. Then we use INTERVAL clause to filter those records where order_date falls after an interval of 15 days before present datetime.
Bonus Read : How to Get Last 12 Months Data in MySQL
How to Get Sign ups & New Users in Last 15 days
Similarly, you can also get new sign ups & users in last 15 days in MySQL. Let’s say you have the following table Users(user_id, date_joined) that contains signup data.
mysql> select * from users; +-------------+---------+ | date_joined | user_id | +-------------+---------+ | 2020-04-20 | 213 | | 2020-04-22 | 214 | | 2020-04-23 | 215 | | 2020-04-24 | 216 | | 2020-04-25 | 217 | | 2020-04-26 | 218 | | 2020-04-27 | 219 | | 2020-04-28 | 220 | | 2020-04-29 | 221 | | 2020-05-30 | 222 | | 2020-05-30 | 222 | | 2020-05-01 | 223 | | 2020-05-01 | 224 | | 2020-05-02 | 225 | | 2020-05-02 | 226 | | 2020-05-03 | 226 | | 2020-05-04 | 227 | | 2020-05-04 | 228 | | 2020-05-05 | 229 | | 2020-05-05 | 230 | | 2020-05-05 | 231 | | 2020-05-05 | 232 | | 2020-05-06 | 233 | | 2020-05-08 | 234 | +-------------+---------+
Bonus Read : How to Create MySQL View
Here’s the SQL query to get daily new sign ups and users in last 15 days in MySQL.
mysql> select * from users
where date_joined> now() - INTERVAL 15 day;
+-------------+---------+
| date_joined | user_id |
+-------------+---------+
| 2020-04-23 | 215 |
| 2020-04-24 | 216 |
| 2020-04-25 | 217 |
| 2020-04-26 | 218 |
| 2020-04-27 | 219 |
| 2020-04-28 | 220 |
| 2020-04-29 | 221 |
| 2020-05-30 | 222 |
| 2020-05-30 | 222 |
| 2020-05-01 | 223 |
| 2020-05-01 | 224 |
| 2020-05-02 | 225 |
| 2020-05-02 | 226 |
| 2020-05-03 | 226 |
| 2020-05-04 | 227 |
| 2020-05-04 | 228 |
| 2020-05-05 | 229 |
| 2020-05-05 | 230 |
| 2020-05-05 | 231 |
| 2020-05-05 | 232 |
| 2020-05-06 | 233 |
| 2020-05-08 | 234 |
+-------------+---------+
In the above query, we use system function now() to get current datetime. Then we use INTERVAL clause to filter those records where order_date falls after an interval of 15 days before present datetime.
That’s it! As you can see it is quite easy to get last 15 days records in MySQL using INTERVAL clause.
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.