select * from pipe where DATEDIFF(pipe_time,NOW())<=0 AND DATEDIFF(pipe_time,NOW())> -5 跟下面是查询出来的数据是一样的 SELECT AVG(pipe_pipe) FROM pipe WHERE TO_DAYS(NOW()) - TO_DAYS(pipe_time) =1(ps:可以根据个人需求更换 参数) 获取以当前为基准获取昨天的数据;
SELECT sum(pipe_pipe)/count(*) FROM pipe WHERE DATE_FORMAT(pipe_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') and pipe_name=#{comparyname}// 获取当月的所有数据
SELECT sum(pipe_pipe)/count(*) FROM pipe WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(pipe_time, '%Y%m')) = 1 and pipe_name=#{comparyname}//获取下月的所有数据
/**
* 当前时间所在一周的周一和周日时间
* @param time 当前时间
* @return
*/
public static Map<String,String> getWeekDate() {
Map<String,String> map = new HashMap();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
cal.setFirstDayOfWeek(Calendar.MONDAY);// 设置一个星期的第一天,按中国的习惯一个星期的第一天是星期一
int dayWeek = cal.get(Calendar.DAY_OF_WEEK);// 获得当前日期是一个星期的第几天
if(dayWeek==1){
dayWeek = 8;
}
System.out.println("要计算日期为:" + sdf.format(cal.getTime())); // 输出要计算日期
cal.add(Calendar.DATE, cal.getFirstDayOfWeek() - dayWeek);// 根据日历的规则,给当前日期减去星期几与一个星期第一天的差值
Date mondayDate = cal.getTime();
String weekBegin = sdf.format(mondayDate);
System.out.println("所在周星期一的日期:" + weekBegin);
cal.add(Calendar.DATE, 4 +cal.getFirstDayOfWeek());
Date sundayDate = cal.getTime();
String weekEnd = sdf.format(sundayDate);
System.out.println("所在周星期日的日期:" + weekEnd);
map.put("mondayDate", weekBegin);
map.put("sundayDate", weekEnd);
return map;
}