springboot+mysql日期分组查询

springboot+mysql 日期分组查询

1.按照天分组查询

@Query(value = "select DATE_FORMAT(exam_time,'%Y-%m-%d') as time,count(*) as count from exam  where exam_time >=:startTime and exam_time<=:endTime group by time", nativeQuery = true)
List<Map<String, Object>> countTrainingByDay(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

其中exam_time是表exam中的字段，startTime、endTime是变量。
查询结果如下：

将startTime与endTime之间时间的数据按照天为单位分组查询，其中只输出数值大于0的map。在需要使用对应天数的数值时，需要先把Object转化为string，在将string转化为int使用。
提取count

parseInt(count.get(i).get("count").toString()) 

2.按照月分组查询

@Query(value = "select DATE_FORMAT(exam_time,'%Y-%m') as time,count(*) as count from exam where exam_time >=:startTime and exam_time<=:endTime group by time", nativeQuery = true)
List<Map<LocalDateTime, Object>> countTrainingByMonth(@Param("startTime"