【MySQL查询当天、前一天、上一年今天、本周、上周、上一年同周、本月、上月、去年同月、今年、上一年数据】

表名为【ec_electric_value】，时间字段为【collect_time】

查询今年

select * from ec_electric_value t where year(t.collect_time) = year(now())

查询上一年

select * from ec_electric_value t where year(t.collect_time) = year(date_sub(now(),interval 1 year))

查询本月

select * from ec_electric_value t where DATE_FORMAT(t.COLLECT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')

查询上月

select * from ec_electric_value t where period_diff(date_format(now(),'%Y%m'), date_format(t.COLLECT_TIME,'%Y%m')) = 1

查询去年同月

select * from ec_electric_value t where date_format(t.COLLECT_TIME,'%Y%m') = date_format((now() - INTERVAL 1 YEAR ),'%Y%m')

查询本周

方法名为【yearweek】，需要注意的是该方法默认一周从周日开始算，该方法有两个参数，格式为【yearweek(date,expr1)】，date表示时间字段，expr1则可以指定星期开始于星期几，expr1也可以不填，缺省为0代表星期从星期天开始，1则代表从星期一开始。

select * from ec_electric_value t where YEARWEEK(date_format(t.COLLECT_TIME,'%Y-%m-%d'),1) = YEARWEEK(now(),1)

查询上周

select * from ec_electric_value t where YEARWEEK(date_format(t.COLLECT_TIME,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1

查询上一年同周

select * from ec_electric_value t where YEARWEEK(date_format(t.COLLECT_TIME,'%Y-%m-%d'),1) = YEARWEEK(date_sub(now(),interval 1 year),1)

查询当天

select * from ec_electric_value t where to_days(t.COLLECT_TIME) = to_days(now())

查询昨天

select * from ec_electric_value t where to_days(now()) - to_days(t.COLLECT_TIME) = 1

查询上一年今天

select * from ec_electric_value t where to_days(t.COLLECT_TIME) = to_days(date_sub(now(),interval 1 year))