Detailed explanation of the code for querying data of a certain day, month, or year in MySQL

today

select * from table name where to_days(time field name) = to_days(now());

Yesterday (including yesterday and today's data)

SELECT * FROM table name WHERE TO_DAYS( NOW( ) ) - TO_DAYS( time field name) <= 1

Yesterday (only includes yesterday)

SELECT * FROM table name WHERE DATEDIFF(field,NOW())=-1; -- Similarly, the query for the day before yesterday is -2

Last 7 days

SELECT * FROM table name where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(time field name)

Last 30 days

SELECT * FROM table name where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(time field name)

this month

SELECT * FROM table name WHERE DATE_FORMAT(time field name, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

Previous month

SELECT * FROM table name WHERE PERIOD_DIFF( date_format( now(), '%Y%m' ) , date_format( time field name, '%Y%m' ) ) = 1

Query data for this quarter

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

Query last quarter's data

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

Query this year's data

select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

Query last year's data

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

Query the data for the current week

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

Query last week's data

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

Query last month's data

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m'); 
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 
select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 
select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 
select * from user where pudate between last day of previous month and first day of next month

Query data for the current month

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

Query data from 6 months ago

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

Summarize

The above is the detailed code for querying data of a certain day, a certain month, and a certain year in MySQL introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!

You may also be interested in:

MySQL implements query results export csv file and import csv file into database operation
Python implements querying MySQL database and sending information via email
JDBC connects to MySql database steps and query, insert, delete, update, etc.
Use SQL statements to query all database names, table names, and field names in MySQL, SQLServer, and Oracle
Detailed explanation of 30 SQL query optimization techniques for MySQL tens of millions of large data
MySQL big data query optimization experience sharing (recommended)

<<: Solution to the problem of var in for loop

>>: How to manually upgrade the kernel in deepin linux