How to Get Data Of Last Day, Week, Month, YEAR in MySQL

To get last day, week, month, year data in mysql; In this tutorial, i am going to show you how to get the last (3, 6, 12, 24) date, week, last 7 days, months, year data using MySQL queries.

How to Get Data Of Last Day, Week, Month, YEAR in MySQL

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 follows:

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

Fetch Last Date Record in MySQL

Use the following mysql to get last date record from database tables; as follows:

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

Get Last WEEK Record in MySQL

Use the following MySQL query to get the last week records from the MySQL database table; as follows:

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

Get Last 7 Day Record in MySQL

Use the following MySQL query for fetching the last 7 days records from the mysql database table; as follows:

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;

Get Day Wise Last Week Data in MySQL

Use the following MySQL query to get day-wise last week data from the mysql database table; as follows:

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)

Get Last Month Record in MySQL

Use the following mysql to query to get the records of the last month in MySQL; as follows:

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 in MySQL

Use the following MySQL query to get the last 3 month records from the MySQL database table; as follows:

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 in MySQL

Use the following mysql query to get last year’s data month wise from the database table; as follows:

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 in MySQL

Use the following Mysql query to get date wise last month’s records from the MySQL database table; as follows:

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 in Mysql

Use the following MySQL query to get the last year records from the mysql database table; as follows:

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 in MySQL

Use the following MySQL query to get year-wise data in MySQL; as follows:

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

Conclusion

To get last day, week, month, year data in mysql; In this tutorial, You have learned how to get the last (3, 6, 12, 24) date, week, last 7 days, months, year data using MySQL queries.

Leave a Comment