mysql最近一天_【易通慧谷】MySql查询最近一天一周等的写法

#查询今天数据

SELECT * FROM 表名 WHERE to_days(时间字段名) = to_days(now());

#查询昨天数据

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

<= 1

#查询7天数据

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY)

<= date(时间字段名)

#查询近30天数据

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY)

<= date(时间字段名)

#查询本月数据

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

DATE_FORMAT(CURDATE() , '%Y%m')

#查询上一月数据

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 enterprise WHERE

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 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 下月第一天 WHERE

date(regdate) = curdate();

SELECT * FROM test WHERE year(regdate)=year(now()) and

month(regdate)=month(now()) and day(regdate)=day(now())

SELECT date(c_instime),curdate() FROM 't_score' WHERE 1 LIMIT 0

, 30