sql语句查询的是按照周统计,但是需求要显示某月的第几周
如下的介绍mysql统计查询的方法
按天统计 select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days; 按月统计 select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months; DATE_FORMAT(date,format) 按天统计 select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks; 或 查看MySQL的manual %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v 其中 1如果周一为一周的第一天,则(小写) DATE_FORMAT(date,'%x %v') 2如果周日为一周的第一天,则(大写) DATE_FORMAT(date,'%X %V') select DATE_FORMAT(date,'%x年-第%v周') as week,sum(money) as money from finance_base where DATE_FORMAT(date,'%Y')=2010 group by week select DATE_FORMAT(date,'%x年-第%v周') as week,sum(money) as money from finance_base where DATE_FORMAT(date,'%Y')=2010 group by week
// 2022-15 2022年第15周 public static String getMonthOfWeekNoByYearWeekNo(String yearWeekNo){ int year = Integer.valueOf(yearWeekNo.split("-")[0]); int weekNo = Integer.valueOf(yearWeekNo.split("-")[1]); Calendar cal =Calendar.getInstance(); cal.set(Calendar.DAY_OF_WEEK,Calendar.MONDAY); cal.set(Calendar.YEAR, year); cal.set(Calendar.WEEK_OF_YEAR, weekNo); // 获取了这周的开始时间 String dateTime = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+ cal.get(Calendar.DAY_OF_MONTH); //System.out.println("开始时间:" + dateTime); return (cal.get(Calendar.MONTH)+1)+"月第"+cal.get(Calendar.WEEK_OF_MONTH)+"周"; } public static void main(String[] args) { System.out.println("转换结果为:" +getMonthOfWeekNoByYearWeekNo("2022-27"));
}
输出结果为: 6月第5周
如下方法的得到当前时间的上几周或者下几周的周一或者周日时间
//根据所传参数,计算前几周或者后几周的周一和周日的日期 //传正数获取未来周一和周日的日期,传负数获取过去周一和周日的日期 public static String getPreOrNextMonday(int num){ SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd"); Calendar calendar1 = Calendar.getInstance(); Calendar calendar2 = Calendar.getInstance(); int dayOfWeek=calendar1.get(Calendar.DAY_OF_WEEK)-1; int offset1=1-dayOfWeek; int offset2=7-dayOfWeek; calendar1.add(Calendar.DATE, offset1+7*num); calendar2.add(Calendar.DATE, offset2+7*num); Date timePreMonday= calendar1.getTime(); Date timeThisMonday = calendar2.getTime(); String preMonday = simpleDateFormat.format(timePreMonday);//获取符合要求格式的上周周一日期 //String nextSunday = simpleDateFormat.format(timeThisMonday);//获取符合要求格式的上周周日的日期 //return preMonday+","+nextSunday; return preMonday; }
标签:week,String,FORMAT,几周,换成,周转,cal,DATE,Calendar From: https://www.cnblogs.com/zhangliang88/p/16724705.html