How to query data within one day in mysql-Mysql Tutorial-php.cn

How to query data within one day in mysql

coldplay.xixi

Release： 2020-10-19 14:20:03

Original

10442 people have browsed it

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].

How to query data within one day in mysql

Mysql method of querying data within a day:

Today

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

Copy after login

Yesterday

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

Copy after login

Nearly 7 days

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

Copy after login

Nearly 30 days

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

Copy after login

This month

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, &#39;%Y%m&#39; ) = DATE_FORMAT( CURDATE( ) , &#39;%Y%m&#39; )

Copy after login

Previous month

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , &#39;%Y%m&#39; ) , date_format( 时间字段名, &#39;%Y%m&#39; ) ) =1

Copy after login

Query this quarter’s data

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

Copy after login

Query last quarter’s data

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

Copy after login

Query this year’s data

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

Copy after login

Query last year’s data

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

Copy after login

Query the current week’s data

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

Copy after login

Query last week’s data

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

Copy after login

Query last month’s data

select name,submittime from enterprise where date_format(submittime,&#39;%Y-%m&#39;)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),&#39;%Y-%m&#39;)
select * from user where DATE_FORMAT(pudate,&#39;%Y%m&#39;) = DATE_FORMAT(CURDATE(),&#39;%Y%m&#39;) ; 
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,&#39;%y-%m-%d&#39;)) = WEEKOFYEAR(now()) 
select * from user where MONTH(FROM_UNIXTIME(pudate,&#39;%y-%m-%d&#39;)) = MONTH(now()) 
select * from user where YEAR(FROM_UNIXTIME(pudate,&#39;%y-%m-%d&#39;)) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,&#39;%y-%m-%d&#39;)) = MONTH(now()) 
select * from user where pudate between  上月最后一天  and 下月第一天

Copy after login

Query the data of the current month

select name,submittime from enterprise   where date_format(submittime,&#39;%Y-%m&#39;)=date_format(now(),&#39;%Y-%m&#39;)

Copy after login

Query the data 6 months from now

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

Copy after login

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!