Detailed introduction to Mysql date query

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

Recommend

Vue+Echart bar chart realizes epidemic data statistics

Table of contents 1. First install echarts in the...

Implementation of new issues of CSS3 selectors

Table of contents Basic Selector Extensions Attri...

Specific steps for Vue browser to return monitoring

Preface When sharing a page, you hope to click th...

Vue implements a simple magnifying glass effect

This article example shares the specific code of ...

In-depth understanding of the vertical-align property and baseline issues in CSS

vertical-align attribute is mainly used to change...

Some notes on mysql create routine permissions

1. If the user has the create routine permission,...

JavaScript to achieve elastic navigation effect

This article shares the specific code for JavaScr...

A complete list of commonly used MySQL functions (classified and summarized)

1. Mathematical Functions ABS(x) returns the abso...

Teach you to connect to MySQL database using eclipse

Preface Since errors always occur, record the pro...

Detailed explanation of Linux tee command usage

The tee command is mainly used to output to stand...

JavaScript event capture bubbling and capture details

Table of contents 1. Event Flow 1. Concept 2. DOM...

How to solve the problem of clicking tomcat9.exe crashing

A reader contacted me and asked why there were pr...

Some notes on installing fastdfs image in docker

1. Prepare the Docker environment 2. Search for f...

IE9beta version browser supports HTML5/CSS3

Some people say that IE9 is Microsoft's secon...