mysql查询从指定日期到指定日期每一天的数据,数据库没有该天的数据也显示
@RequestMapping(value = "/queryWorkScore", method = RequestMethod.GET)
public List queryWorkScore(String startTime, String endTime) {
int num = calcBetweenDate(startTime, endTime);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select date_add(?,interval @i:=@i+1 day) as date \n" +
" from (\n " +
" select 1 ");
for (int i = 0; i < num; i++) {
stringBuilder.append(" union all select 1 \n");
}
stringBuilder.append(" ) as tmp, (select @i:= -1) t");
List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(stringBuilder.toString(), new Object[]{startTime});
return queryForList;
}
public int calcBetweenDate(String start, String end) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Date startDate = null;
Date endDate = null;
try {
startDate = df.parse(start);
endDate = df.parse(end);
} catch (Exception e) {
System.out.println("日期转换出错");
}
int count = (int) ((endDate.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000));
return count;
}