项目 - 如何根据最近30天、最近14天、最近7天、最近24小时、自定义时间范围查询MySQL中的数据？

这个需求在工作中常常遇到，之前是针对es中的数据进行查询，现在是针对mysql中的数据进行查询，原理一样。

@AllArgsConstructor
public enum TimeScopeEnum {
    
    LAST_30_DAYS("last_30_days",  30),

    
    LAST_14_DAYS("last_14_days",14),

    
    LAST_7_DAYS("last_7_days",7),

    
    LAST_24_HOURS("last_24_hours",1);

    
    @Getter
    private String name;

    
    @Getter
    private Integer value;

    
    public static TimeScopeEnum getTimeScopeEnumByName(String name){
        for (TimeScopeEnum timeScopeEnum : TimeScopeEnum.values()) {
            if(timeScopeEnum.name.equals(name)){
                return timeScopeEnum;
            }
        }
        return null;
    }
}

查询最近7天的数据，前端请求路径：

endTime=1651288728694&startTime=1650683928694&timeScope=last_7_days

根据自定义时间区间查询，前端请求路径：

endTime=1652803199999&startTime=1649865600000&timeScope=custom

@Data
@AllArgsConstructor
@NoArgsConstructor
public class AssetsStatisticQo {

    @ApiModelProperty("开始时间")
    @NotNull(message = "开始时间不能为空")
    private Long startTimeStamp;

    @ApiModelProperty("结束时间")
    @NotNull(message = "结束时间不能为空")
    private Long endTimeStamp;

    @NotBlank
    @ApiModelProperty("时间范围")
    private String timeScope;
}

public class Constant {

    
    public static final String CUSTOM_QUERY_TIME = "custom";
}

@Slf4j
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TimeArgumentQo {

    
    private final Date now = new Date();

    
    private Date startDate;

    
    private Date endDate;

    
    private TimeScopeEnum timeScopeEnum;

    
    public TimeArgumentQo(Long startTimeStamp, Long endTimeStamp, String timeScopeEnumStr){
        if(StringUtils.isEmpty(timeScopeEnumStr)){
            log.error("input time parameter error. timeScopeStr: {}", timeScopeEnumStr);
            throw new BusinessException("input.params.time.error");
        }
        // 如果是自定义查询时间
        if (Constant.CUSTOM_QUERY_TIME.equals(timeScopeEnumStr)) {
            if (startTimeStamp != null && startTimeStamp > 0L) {
                startDate = DateUtil.date(startTimeStamp);
            }
            if (endTimeStamp != null && endTimeStamp > 0L) {
                endDate = DateUtil.date(endTimeStamp);
            }
            boolean flag = startDate != null && endDate != null && (startDate.equals(endDate) || startDate.after(endDate));
            if (flag) {
                log.error("input time parameter error. startDate: {}, endDate: {}", startDate, endDate);
                throw new BusinessException("input.params.time.error");
            }
        } else {
            // 如果不是自定义查询时间
            timeScopeEnum = TimeScopeEnum.getTimeScopeEnumByName(timeScopeEnumStr);
            if (Objects.isNull(timeScopeEnum)) {
                log.error("input time parameter error. timeScope: {}.", timeScopeEnumStr);
                throw new BusinessException("input.params.time.error");
            }
            if (TimeScopeEnum.LAST_24_HOURS.equals(timeScopeEnum) || TimeScopeEnum.LAST_7_DAYS.equals(timeScopeEnum) 
                    || TimeScopeEnum.LAST_14_DAYS.equals(timeScopeEnum)
                    || TimeScopeEnum.LAST_30_DAYS.equals(timeScopeEnum)) {
                
                startDate = DateUtil.offsetDay(now, -timeScopeEnum.getValue());
                endDate = now;
            } else {
                
                startDate = DateUtil.offsetMonth(now, -timeScopeEnum.getValue());
                endDate = now;
            }
        }
    }
}

① Controller层

@Slf4j
@ResponseResult
@RequestMapping("/api/v1/assetsStatistics")
public class AssetsStatisticController {

    @Autowired
    private IAssetsStatisticService assetsStatisticService;

    
    @PreAuthorize("hasAnyAuthority('superAdmin','safeOperationQuery')")
    @GetMapping("/list")
    public List queryList(@Validated @ModelAttribute AssetsStatisticQo assetsStatisticQo) {
        return assetsStatisticService.queryList(assetsStatisticQo);
    }
}

② Service层

@Slf4j
@Service("assetsStatisticService")
public class AssetsStatisticServiceImpl implements IAssetsStatisticService {

    @Autowired
    private IAssetsStatisticDao assetsStatisticDao;

    
    @Override
    public List queryList(AssetsStatisticQo assetsStatisticQo) {
        // 转换时间区间
        TimeArgumentQo timeArgumentQo = new TimeArgumentQo(
                assetsStatisticQo.getStartTime(),
                assetsStatisticQo.getEndTime(),
                assetsStatisticQo.getTimeScope()
        );
        Date startDate = timeArgumentQo.getStartDate();
        Date endDate = timeArgumentQo.getEndDate();
        List assetsStatisticVoList = assetsStatisticDao.statisticBranchAssets( startDate, endDate);
        return assetsStatisticVoList;
    }
}

③ dao层：

@Repository
public interface IAssetsStatisticDao {

    
    List queryBranchAssets( @Param("startDate") Date startDate, @Param("endDate") Date endDate);

}

    select  from t_assets_statistic
    
        
            and storageTime ">>= #{startDate}
        
        
            and storageTime <= #{endDate}