按时间区间查询数据，并取interval间隔时间的第一条

用多了MP，mybatis语句的这种sql语句不太熟练，记录一下

查询某一条船的历史轨迹

地址：

GET /xxxx/xxxx/xxxx

参数：

@GetMapping(value = "/xxx/xxx/xxx")
    public Map getHistoryLocationAndStatus(
            @RequestParam(value = "shipName",required = true) String shipName,
            @RequestParam(value = "date",required = false) Date date,
            @RequestParam(value = "interval",required = false) String interval
    )
    {
        List list = shipManageService.getHistoryLocationAndStatus(shipName, date ,interval);
        Map resultMap = new HashMap<>();
        resultMap.put("information", list);
        resultMap.put("shipName", shipName);
        return resultMap;
    }

  
    List getHistoryLocationAndStatus(String shipName, Date date,String interval);

@Override
    public List getHistoryLocationAndStatus(String shipName, Date date,String interval) {
        //判断传过来的时间是否为空，如果为空直接默认当日前一天
        if (date == null) {
            DateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Calendar calendar=Calendar.getInstance();
            calendar.set(Calendar.HOUR_OF_DAY,-1);
//            String yesterdayDate=dateFormat.format(calendar.getTime());
//            System.out.println("yestodayDate:"+yesterdayDate);
            date = calendar.getTime();
            System.out.println(date);
        }else{
            //判断时间是否为一周之内的时间，如果不是提示出错
            Date nowDate = DateUtils.getNowDate();
            long datePoor = DateUtils.getDatePoorDay(date, nowDate);
            if(datePoor>6){
                throw new RuntimeException("您查询的船只时间已过期，请查询近7日以内的船只历史记录");
            }
        }
        if (StringUtils.isBlank(interval)) {
            interval = "5";
        }
        Date startTime = DateUtils.weeHours(date, 0);
        Date endTime = DateUtils.weeHours(date, 1);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sTime = sdf.format(startTime);
        String eTime = sdf.format(endTime);
        return shipMapper.getHistoryLocationAndStatus(shipName,sTime,eTime,Integer.parseInt(interval));
    }

    
    List getHistoryLocationAndStatus(String shipName,String startTime,String endTime,int interval);

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class ShipStatusVo {

    
    private String shipName;
    
    private Date time;

    
    private String longtitude;

    
    private String latitude;

    
    private String speed;

    
    private String warningInformation;
}

 
        
        
        
        
        
        
 

        select ship_name,update_time,longtitude, latitude, speed, warning_information from tracktable
        where  date_format(update_time,'%i')%#{interval}=0
          and  (update_time between #{startTime} and #{endTime})
          and (ship_name = #{shipName})
        group by date_format(update_time,'%Y-%m-%d %H:%i')