How to limit sql query time in mysql

Mysql method to limit SQL query time: 1. To query today, the code is [select * from table name where to_days (time field name) = to_days(now())]; 2. To query yesterday, the code is [SELECT * FROM table name WHERE].

The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22. This method is suitable for all brands of computers.

Related free learning recommendations: mysql video tutorial

Mysql method to limit sql query time:

Today

select * from 表名 where to_days(时间字段名) = to_days(now());

Yesterday

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

Nearly 7 days

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

Nearly 30 days

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

This month

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )

Previous month

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1

Query this quarter’s data

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 the previous year’s data

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

Query the current week’s data

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 上月最后一天 and 下月第一天

Query current month’s data

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

Related free learning recommendations: php programming (video)

The above is the detailed content of How to limit sql query time in mysql. For more information, please follow other related articles on the PHP Chinese website!