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

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

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

需求：接口说明：

查询某一条船的历史轨迹

地址：

GET /xxxx/xxxx/xxxx

参数：

参数名	类型	说明	范围	是否必需
shipName	字符串	船名	“001”-”100”	是
date	时间戳	日期	最近一周的某一天，默认当日的前一天	否
interval	字符串	时间间隔	“1”-”60”，默认间隔5分钟，最短间隔1分钟，最长间隔60分钟	否

1 controller

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

2.service

  /**
     * 
     * @param shipName
     * @param date
     * @param interval
     * @return
     */
    List<ShipStatusVo> getHistoryLocationAndStatus(String shipName, Date date,String interval);

3.impl

@Override
    public List<ShipStatusVo> 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));
    }

4.mapper

    /**
     * 4、查询某一条船的历史轨迹
     * @param shipName
     * @param
     * @return
     */
    List<ShipStatusVo> getHistoryLocationAndStatus(String shipName,String startTime,String endTime,int interval);

5.vo

@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;
}

6.xml

 <resultMap type="com.xidian.ship.vo.CurrentShipLocationVo" id="CurrentLocationResult">
        <result property="shipName"    column="ship_name"    />
        <result property="time"    column="update_time"    />
        <result property="longtitude"    column="longtitude"    />
        <result property="latitude"    column="latitude"    />
        <result property="speed"    column="speed"    />
        <result property="warningInformation"    column="warning_information"    />
 </resultMap>
<select id="getHistoryLocationAndStatus" resultType="com.xxxxx.ship.vo.ShipStatusVo" resultMap="CurrentLocationResult">
        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')
 </select>