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
This article describes the MySQL data types and f...
In the horizontal direction, you can set the alig...
A few days ago, when I was working on a requireme...
I downloaded and installed the latest version of ...
1. Hot deployment: It means redeploying the entir...
As the domestic network environment continues to ...
This article example shares the specific code of ...
This article shares the specific code of jQuery t...
In the previous article, we used Docker to build ...
If you want to install multiple tomcats, you must...
1. Introduction A few days ago, a development col...
Before introducing the GROUP BY and HAVING clause...
Loading kernel symbols using gdb arm-eabi-gdb out...
1. First, prepare VS2019 and MySQL database. Both...
Don't be surprised if you see some kind of und...