Mysql Query to Get Data Of Last Day, Week, Month, YEAR

To get last day, week, month, year data in mysql; In this tutorial, we would love to share with you how to get the last date, last week, last 7 days, last, month, last 3,6,9,12 months, last year data using MySQL queries.

We will also take few more examples of MySQL last date/day, week, month, year, e.g. Day/Date wise, last week wise data, get month wise last year data, day-wise last month data, year-wise date.

When we create analytics of any orders, users purchase, we need to show data daily basis, weekly basis, monthly basis, yearly basis. Here you will learn how to fetch last day basis records, last weekly basis record, last monthly basis record, etc from MySQL database tables.

For example, If you are working with any e-commerce application. So you need to show date basis orders profit or loss analytics, last weekly basis profit or loss analytics, last monthly basis profit or loss analytics, etc.

Mysql Query to Get Data Of Last Day, Week, Month, YEAR

Use the following MySQL queries to get the last date, last week, last 7 days, last, month, last 1,3,6,9,12 months, last year data in MySQL; as shown below:

• Fetch Last Date Record
• Fetch Last WEEK Record
• Get Last 7 Day Record
• Fetch Day Wise Last Week Data
• Fetch Last Month Record
• Get Last 3 Month Record
• Fetch Month Wise Last Year Data
• Fetch Day Wise Last Month Data
• Fetch Last Year Record
• Year Wise Date

Fetch Last Date Record

If you want to fetch last date record from database tables. Use the below MySQL Query for fetching the last date records.

SELECT name, created_at
FROM employees
WHERE DATE(created_at) = DATE(NOW()) ORDER BY `id` DESC

Fetch Last WEEK Record

Using the below MySQL query for fetching the last week records from the MySQL database table.

SELECT name, created_at
FROM employees
WHERE
YEARWEEK(`created_at`, 1) = YEARWEEK( CURDATE() - INTERVAL 1 WEEK, 1)

Get Last 7 Day Record

Using the below MySQL query for fetching the last 7 days records from the mysql database table.

If you want to get the last 10 days or the last 15 days records from a database table, you can change the query accordingly.

SELECT name, created_at
FROM employees
WHERE created_at >= DATE(NOW()) - INTERVAL 7 DAY

=====================OR=================================

SELECT name, created_at
FROM employees
WHERE created_at >= DATE_SUB(DATE(NOW()), INTERVAL 7 DAY)
ORDER BY created_at DESC;

Fetch Day Wise LastWeek Data

Fetch day-wise last week data from the mysql database table. Use the below MySQL query for that.

SELECT DATE(created_at) as Date, DAYNAME(created_at) as 'Day Name', COUNT(id) as Count
FROM employees
WHERE date(created_at) < DATE_SUB(NOW(), INTERVAL 1 WEEK) AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE())
GROUP BY DAYNAME(created_at) ORDER BY (created_at)

Fetch Last Month Record

Fetch the records of the last month in MySQL. Use the below query that finds the last month’s records from the MySQL database table.

SELECT name, created_at as create_date
FROM employees
WHERE MONTH(created_at) = MONTH(NOW()) - 1 ORDER BY `id` DESC

=======================OR======================================

SELECT * FROM employees
WHERE YEAR(created_at) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(created_at) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

Get Last 3 Month Record

Using the below MySQL query for fetching the last 3 month records from the MySQL database table.

If you want to get last 3 month or 6-month records from the database table, you can change the query accordingly.

SELECT *
FROM employees
WHERE created_at > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)

=====================OR=================================

SELECT name, created_at
FROM employees
WHERE created_at >= DATE(NOW()) - INTERVAL 3 MONTH

Fetch Month Wise Last Year Data

If you want to get last year’s data month wise from the database table. Use the below MySQL query for fetch the records from month wise of last year.

SELECT COUNT(id) as Count,MONTHNAME(created_at) as 'Month Name', YEAR(created_at) as Year
FROM employees
WHERE YEAR(created_at) = YEAR(CURDATE()- INTERVAL 1 YEAR)
GROUP BY YEAR(created_at),MONTH(created_at)

Fetch Date Wise Last Month Data

Fetch date wise last month’s records from the MySQL database table. Use the below MySQL query for fetch the last month’s records from date wise.

SELECT COUNT(id) as Count, DAY(created_at) as 'Day', DAYNAME(created_at) as 'Day Name', MONTHNAME(created_at) as 'Month Name'
FROM employees
WHERE MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH)
AND YEAR(created_at) = YEAR(CURDATE())
GROUP BY DAY(created_at)

Fetch Last Year Record

You want to fetch the last year records from the mysql database table. Use the below to get last year’s records from the database table.

SELECT name, created_at, YEAR(created_at) as year
FROM employees
WHERE YEAR(create_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));

=================================OR====================================

SELECT name, created_at, YEAR(created_at) as year
FROM employees
WHERE YEAR(created_at) = YEAR(NOW() - INTERVAL 1 YEAR) ORDER BY `id` DESC

Year Wise Data

You want to fetch year-wise data. Use the below query for getting year-wise data from the mysql database table.

SELECT COUNT(id) as Count,YEAR(created_at) as Year
FROM employees
GROUP BY YEAR(created_at)

Conclusion

Here, you have learned how to fetch data last day, date, week, month, year-wise with examples. Also, we have explained how to get last 7 day, 15 days, last 3 month, last 6 month records from the MySQL database.

Recommended MySQL Tutorials

If you have any questions or thoughts to share, use the comment form below to reach us.