mysql数据库查询当天,最近7天,最近1个月数据【杭州多测师】【杭州多测师_王sir】

查询当天数据
select * from tab where FROM_UNIXTIME(fabutime, %Y%m%d) = 20121217;
 
mysql TO_DAYS(date) 函数
TO_DAYS(date)
给定一个日期date, 返回一个天数 (从年份0开始的天数 )。

最近一个月
SELECT * FROM table WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(time);   
本月、当前月
SELECT * FROM table WHERE DATE_FORMAT(o.createTime,%Y-%m) = DATE_FORMAT(CURDATE(),%Y-%m);   
上个月
SELECT * FROM table WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),%Y-%M),DATE_FORMAT(create_time,%Y-%m)) = 1;  
最近一周、7天
SELECT * FROM table WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(time);   
最近一天
SELECT * FROM table WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= date(time);   
当天
SELECT * FROM table WHERE to_days(create_time) = to_days(now()); 

#查询本季度数据
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)); 

 

mysql数据库查询当天,最近7天,最近1个月数据【杭州多测师】【杭州多测师_王sir】

上一篇:H2数据库Rce利用


下一篇:MySQL命令行操作