MYSQL query time data

Using mysql to query time data is often encountered in daily use of PHP. This article will introduce the query of various time data.

mysql query this quarter

Today

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

Yesterday

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

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

#查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());

查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1;

查询当前月份的数据
select name,submittime from enterprisewhere date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)

查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查询上个月的数据
select name,submittime from enterprisewhere date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’)

select*from`user`whereDATE_FORMAT(pudate,‘%Y%m‘)=DATE_FORMAT(CURDATE(),‘%Y%m‘) ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = WEEKOFYEAR(now())

select*
fromuser
whereMONTH(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=MONTH(now())

select*
from[user]
whereYEAR(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=YEAR(now())
andMONTH(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=MONTH(now())

select*
from[user]
wherepudatebetween上月最后一天
and下月第一天

wheredate(regdate)=curdate();

select*fromtestwhereyear(regdate)=year(now())andmonth(regdate)=month(now())andday(regdate)=day(now())

This article introduces a number of commonly used time data. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

How to use database methods to save sessions

Five ways to prevent SQL injection

About MySQL high availability: Keepalived dual master hot standby

The above is the detailed content of MYSQL query time data. For more information, please follow other related articles on the PHP Chinese website!