How to Get Records of Current Month in MySQL

Last updated on June 8th, 2020 at 09:26 am

It helps to get records of current month in MySQL for reporting and data analysis. You can use it to get sales data for current month, number of sign ups in current month and other useful information. Here’s how to get rows of current month in MySQL.

Table of Contents

How to Get Records of Current Month in MySQL

Here are the steps to get current month records in MySQL. We will also use it to get new customers in the month.

Let’s say you have the following table sales(order_date, sale, orders) that contains daily number of orders and sale amount.

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

Bonus Read : How to Import CSV into MySQL Workbench

How to Get Sales Data of Current Month in MySQL

Here’s the SQL query to get records of current month in MySQL

mysql> select * from sales
       where MONTH(order_date)=MONTH(now())
       and YEAR(order_date)=YEAR(now());
+------------+------+--------+
| order_date | sale | orders |
+------------+------+--------+
| 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 |  200 |     18 |
+------------+------+--------+

In the above query, we use system function now() to get current datetime. Then we get current month rows in MySQL by filtering rows which has the same month and year as current datetime. MONTH() and YEAR() are in-built MySQL functions to get month number and year number from a given date.

Bonus Read : How to Create Histogram in MySQL

If your date column, that is, order_date is indexed, then it is advisable not to use any function on it. Otherwise, it will take a long time to run. Instead, you can use the following SQL query to get records of current month in MySQL.

mysql> SELECT * FROM sales
      WHERE order_date >= (LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH)
      AND order_date <  (LAST_DAY(NOW()) + INTERVAL 1 DAY);
+------------+------+--------+
| order_date | sale | orders |
+------------+------+--------+
| 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 |  200 |     18 |
+------------+------+--------+

After you get records of current month in MySQL, you can use a charting tool to plot it in a good-looking graph as shown below and share it with your team. Here’s a line graph created using Ubiq dashboard software

Bonus Read : How to Calculate Conversion Rate in MySQL

How to Get Sign ups & New Users in Current Month in MySQL

Similarly, you can also get new sign ups & users in current month 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-28  |     213 |
| 2020-04-28  |     214 |
| 2020-04-30  |     215 |
| 2020-04-28  |     216 |
| 2020-04-28  |     217 |
| 2020-04-30  |     218 |
| 2020-04-28  |     219 |
| 2020-04-28  |     220 |
| 2020-04-30  |     221 |
| 2020-05-01  |     222 |
| 2020-05-01  |     222 |
| 2020-05-01  |     223 |
| 2020-05-04  |     224 |
| 2020-05-04  |     225 |
| 2020-05-04  |     226 |
| 2020-05-04  |     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-06  |     234 |
+-------------+---------+

Here’s the SQL query to get daily new sign ups and users in current month in MySQL.

mysql> select date(date_joined),count(*) from users
       where MONTH(date_joined)=MONTH(now())
       and YEAR(date_joined)=YEAR(now())
       group by date(date_joined);
+-------------------+----------+
| date(date_joined) | count(*) |
+-------------------+----------+
| 2020-05-01        |        3 |
| 2020-05-04        |        6 |
| 2020-05-05        |        4 |
| 2020-05-06        |        2 |
+-------------------+----------+

You can also use this SQL query to get count of users in a month or count new users in last month. Just remove date(date_joined) from select clause and remove group by clause to get total count.

mysql> select count(*) from users
       where MONTH(date_joined)=MONTH(now())
       and YEAR(date_joined)=YEAR(now());

Bonus Read : How to Fill Missing Dates in MySQL

After you get records of current month in MySQL, you can use a reporting tool to plot this data on a bar chart or dashboard and share it with your team. Here’s an example of a bar chart that shows daily signups in current month, created using Ubiq.