Detailed introduction to Mysql date query

Query the current date

SELECT CURRENT_DATE();
SELECT CURDATE();

Query the current date and time

SELECT NOW();

Query today's data

SELECT * FROM `table name` WHERE TO_DAYS(NOW()) = TO_DAYS(`field`);
SELECT * FROM `table name` WHERE TO_DAYS(NOW()) - TO_DAYS(`field`) = 0;

Query yesterday's data

SELECT * FROM ``table name`` WHERE TO_DAYS(`field`) = TO_DAYS(NOW()) -1;
SELECT * FROM ``table name`` WHERE TO_DAYS(`field`) - TO_DAYS(NOW()) = -1;
SELECT * FROM ``table name`` WHERE TO_DAYS(NOW()) = TO_DAYS(`field`) +1;
SELECT * FROM ``table name`` WHERE TO_DAYS(NOW()) - TO_DAYS(`field`) = 1;

Query the data for the last seven days

SELECT * FROM `table name` WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(`field`);

Query the data for the last thirty days

SELECT * FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(`field`);

Query this week's data

SELECT * FROM `table name` WHERE YEARWEEK(date_format(`field`,'%Y-%m-%d')) = YEARWEEK(CURDATE());
SELECT * FROM `table name` WHERE YEARWEEK(date_format(`field`,'%Y-%m-%d')) - YEARWEEK(CURDATE()) = 0;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) = YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d'));
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) - YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d')) = 0;

Query last week's data

SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) = YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d')) - 1;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) - YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d')) = -1;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) = YEARWEEK(CURDATE()) - 1;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) - YEARWEEK(CURDATE()) = -1;

Query data for the current month

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y-%m') = DATE_FORMAT(CURDATE(),'%Y-%m');
SELECT * FROM `table name` WHERE MONTH(`field`) - MONTH(NOW()) = 0;
SELECT * FROM `table name` WHERE MONTH(`field`) = MONTH(NOW());

Query last month's data

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y-%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y-%m');

Query data for this quarter

SELECT * FROM `table name` WHERE QUARTER(`field`) = QUARTER(NOW()) AND YEAR(`field`) = YEAR(NOW());

Query the data of the previous quarter

SELECT * FROM `table name` WHERE QUARTER(`field`) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) AND YEAR(`field`) = YEAR(NOW());

Query data for the first half of the year (six months)

SELECT * FROM `table name` WHERE `field` BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();

Query this year's data

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y') = DATE_FORMAT(NOW(),'%Y');
SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y') - DATE_FORMAT(NOW(),'%Y') = 0;
SELECT * FROM `table name` WHERE YEAR(NOW()) = YEAR(`field`);
SELECT * FROM `table name` WHERE YEAR(NOW()) - YEAR(`field`) =0;

Query last year's data

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y') - DATE_FORMAT(NOW(),'%Y') = -1;
SELECT * FROM `table name` WHERE YEAR(NOW()) = YEAR(`field`) + 1;
SELECT * FROM `table name` WHERE YEAR(NOW()) - YEAR(`field`) = 1;

This is the end of this article about the detailed introduction of MySQL date query. For more relevant MySQL date query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

Detailed explanation of date formatting in MySQL
Detailed explanation of mysql get current date function and time formatting parameters
How to query date and time in mysql
How to query records between two dates in MySQL
Detailed explanation of MySql date query statement
Mysql date formatting and complex date range query

<<: How to dynamically add modules to Nginx

>>: Vue el-date-picker dynamic limit time range case detailed explanation