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:
|
<<: How to dynamically add modules to Nginx
>>: Vue el-date-picker dynamic limit time range case detailed explanation
Given an array [1,8,5,4,3,9,2], write an algorith...
Table of contents 1. First install echarts in the...
Table of contents Basic Selector Extensions Attri...
Preface When sharing a page, you hope to click th...
This article example shares the specific code of ...
vertical-align attribute is mainly used to change...
1. If the user has the create routine permission,...
This article shares the specific code for JavaScr...
1. Mathematical Functions ABS(x) returns the abso...
Preface Since errors always occur, record the pro...
The tee command is mainly used to output to stand...
Table of contents 1. Event Flow 1. Concept 2. DOM...
A reader contacted me and asked why there were pr...
1. Prepare the Docker environment 2. Search for f...
Some people say that IE9 is Microsoft's secon...