How to query data within one day in mysql

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!