mysql查询今天、昨天、7天、近30天、本月、上一月 数据

获取当前时间

CURTIME();

查询今天的数据

select * from table where to_days(time) = to_days(now())

查询昨天数据

select * from error where to_days(now())-to_days(alarmtime)=1

查询最近一个星期数据

select * from error where to_days(now())-to_days(alarmtime)<=7

SELECT * FROM error where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(alarmtime)

查询某天后一个星期的数据

SELECT * FROM error where DATE_ADD('2018-12-13', INTERVAL 7 DAY)

以上查询语句若有一天没有数据就没有那一天的显示

要想每天都显示 空的补0 可以用下面的sql 

构建一个最近七天的结果集，然后和查询的结果集合做left join

select a.click_date,ifnull(b.count1,0)as count1 from (
SELECT curdate() as click_date
union all SELECT date_sub(curdate(), interval 1 day) as click_date
union all SELECT date_sub(curdate(), interval 2 day) as click_date
union all SELECT date_sub(curdate(), interval 3 day) as click_date
union all SELECT date_sub(curdate(), interval 4 day) as click_date
union all SELECT date_sub(curdate(), interval 5 day) as click_date
union all SELECT date_sub(curdate(), interval 6 day) as click_date ) a
left join ( select date(alarmtime) as datetime, count(*) as count1 from error  where type=1 group by date(alarmtime) ) b on a.click_date = b.datetime

 

转载于:https://www.cnblogs.com/liangb/p/10144133.html