Mysql method of querying data within a day: 1. Query today's [select * from table name where to_days (time field name) = to_days(now())]; 2. Query yesterday's [SELECT * FROM table name WHERE TO_DAYS].
Mysql method of querying data within a day:
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 last 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 the data of the current month
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
Query the data 6 months from now
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
Related free learning recommendations: mysql database(video)
The above is the detailed content of How to query data within one day in mysql. For more information, please follow other related articles on the PHP Chinese website!