因为有的时候时间这块我容易出错,所以记录下来规避日后疑惑和汇总记录一下方便日后使用,希望对大家也有一定的帮助!
实体类【时间给的是String类型】
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import java.time.LocalDate;
import java.util.Date;
@Data
public class PartnerControlVerifyListReq {
@Schema(description = "证件类型 0身份证 1港澳通行证 2台湾通行证 ")
private Integer identifiType;
@Schema(description = "用户ID")
private Long uid;
@Schema(description = "用户姓名")
private String username;
@Schema(description = "用户手机")
private Long phone;
@Schema(description = "审核状态")
private Integer realStatus;
@Schema(description = "时间类型:1.日 2.周3.月")
private Integer loginDateType;
@Schema(description = "开始时间")
private String startTime;
@Schema(description = "结束时间")
private String endTime;
@Schema(description = "页数" )
private Integer curPage;
@Schema(description = "页条数" )
private Integer pageNum;
}
业务代码
@SneakyThrows
@Override
public CommonResult<ControlVerifyListResp> controlVerifyList(ControlVerifyListReq req) {
ControlVerifyListResp resp = new ControlVerifyListResp();
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
if (Objects.nonNull(req.getAllType())) {
if(req.getAllType()==ConstantUtil.Date_Local){//选择某天查询
if (Objects.nonNull(req.getStartTime())) {
lambdaQueryWrapper.ge(User::getLoginTime, req.getStartTime()).lt(User::getLoginTime,getAfterDay(req.getStartTime()));
}
}
if(req.getAllType()==ConstantUtil.Date_week){//一周区间查询
//登录时间区间
if (Objects.nonNull(req.getStartTime())) {
lambdaQueryWrapper.apply(StrUtil.isNotBlank(req.getStartTime()), "date_format (login_time,'%Y-%m-%d') >= date_format('" + req.getStartTime() + "','%Y-%m-%d')");
}
if (Objects.nonNull(req.getEndTime())) {
lambdaQueryWrapper.apply(StrUtil.isNotBlank(req.getEndTime()),
"date_format (login_time,'%Y-%m-%d') <= date_format('" + req.getEndTime() + "','%Y-%m-%d')");
}
}
if(req.getAllType()==ConstantUtil.Date_month){//指定月份查询
if (Objects.nonNull(req.getStartTime())) {
lambdaQueryWrapper.like(User::getLoginTime,req.getStartTime());
}
}
if(req.getAllType()==ConstantUtil.REAL_SUCCESS_TYPE){//已认证
lambdaQueryWrapper.eq(User::getRealStatus, ConstantUtil.REAL_SUCCESS_DATABASE);
}
if(req.getAllType()==ConstantUtil.REAL_NO_TYPE){//已未认证
lambdaQueryWrapper.eq(User::getRealStatus, ConstantUtil.REAL_NO_DATABASE);
}
if(req.getAllType()==ConstantUtil.EXAMINE_NO){//待审核
lambdaQueryWrapper.eq(User::getRealStatus, ConstantUtil.EXAMINE_NO_DATABASE);
}
if(req.getAllType()==ConstantUtil.EXAMINE_FAIL){//审核失败
lambdaQueryWrapper.eq(User::getRealStatus, ConstantUtil.EXAMINE_FAIL_DATABASE);
}
}
//根据审核状态 和 时间倒序排序
lambdaQueryWrapper.orderByDesc(User::getRealStatus);
lambdaQueryWrapper.orderByDesc(User::getCreateTime);
// 2. 分页查询数据
Page<User> page = new Page<>(req.getCurPage(), Math.min(req.getPageNum(), PageContants.PAGE_MAX_NUM));
IPage<User> pageList = userMapper.selectPage(page, lambdaQueryWrapper);
// 3. 封装 DTO
List<ControlVerifyListDto> ControlVerifyListDtos = new ArrayList<>();
for (User user : pageList.getRecords()) {
ControlVerifyListDto ControlVerifyListDto = new ControlVerifyListDto();
BeanUtils.copyProperties(user, partnerControlVerifyListDto);
ControlVerifyListDto.setPhone(user.getPhone()+"");
ControlVerifyListDtos.add(ControlVerifyListDto);
}
// 4. 封装 Resp
resp.setTotal(pageList.getTotal());
resp.setCurPage(req.getCurPage());
resp.setControlVerifyListDto(partnerControlVerifyListDtos);
LOGGER.info("===============实名认证审核列表返回信息==========" + resp.toString());
return CommonResult.success(resp);
}
sql语句
login_time是你们要查询的时间字段
一、查询当天的记录:
select count(*) from 表名 where TO_DAYS(login_time) = TO_DAYS(NOW())
二、查询当前这周的数据:
外国计算:
SELECT * FROM 表名 WHERE YEARWEEK(date_format(login_time,'%Y-%m-%d')) = YEARWEEK(now())
中国周计算:(外国和中国周计算不同)
SELECT * FROM 表名 WHERE YEARWEEK(date_format(login_time,"%Y-%m-%d"),1) = YEARWEEK(now(),1)
条数
SELECT count(uid) FROM 表名 WHERE YEARWEEK(date_format(login_time,"%Y-%m-%d"),1) = YEARWEEK(now(),1)
三、查询本月的数据:
SELECT * FROM 表名 WHERE DATE_FORMAT( login_time, "%Y%m" ) = DATE_FORMAT( CURDATE( ) , "%Y%m" )
获取条数
SELECT count(*) FROM 表名 WHERE DATE_FORMAT( login_time, "%Y%m" ) = DATE_FORMAT( CURDATE( ) , "%Y%m" )
日期范围查询
第一种可以用【ge le】
ge大于等于>= le小于等于 gt大于 lt小于
注:这里的实体类时间类型给的是String!
getStartTime和getEndTime
//登录时间区间
if (Objects.nonNull(req.getStartTime())) {
lambdaQueryWrapper.ge(User::getLoginTime,req.getStartTime());
}
if (Objects.nonNull(req.getEndTime())) {
lambdaQueryWrapper.lt(User::getLoginTime,req.getEndTime());
}
第二种
时间类型给的是LocalDate:startTime和endTime
//创建时间
if (Objects.nonNull(startTime)) {
queryWrapper.apply((ObjectUtil.isNotNull(startTime)), "DATE_FORMAT(create_time, '%Y-%m-%d') >= {0}", startTime);
}
if (Objects.nonNull(endTime)) {
queryWrapper.apply((ObjectUtil.isNotNull(endTime)), "DATE_FORMAT(create_time, '%Y-%m-%d') <= {0}", endTime);
}
3.这个是我使用的
sql语句
select count(*) from user
where
date_format(login_time,‘%Y-%m-%d’) >= str_to_date(“2022-05-23”,‘%Y-%m-%d’)
and
date_format(login_time,‘%Y-%m-%d’) <= str_to_date(“2022-05-31”,‘%Y-%m-%d’)
java业务代码【req.getStartTime()和getEndTime()类型都是String类型】,查询范围准确
//登录时间区间
if (Objects.nonNull(req.getStartTime())) {
lambdaQueryWrapper.apply(StrUtil.isNotBlank(req.getStartTime()), "date_format (login_time,'%Y-%m-%d') >= date_format('" + req.getStartTime() + "','%Y-%m-%d')");
}
if (Objects.nonNull(req.getEndTime())) {
lambdaQueryWrapper.apply(StrUtil.isNotBlank(req.getEndTime()),
"date_format (login_time,'%Y-%m-%d') <= date_format('" + req.getEndTime() + "','%Y-%m-%d')");
}
SimpleDateFormat 时间格式
年yyyy月MM日dd时HH分mm秒ss毫秒SS
String转Date
Date date1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2020-11-01 08:30:20");
Date转String
Date date2 = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String str = sdf.format(date2);
String转LocalDate
LocalDate beginDateTime = LocalDate.parse(beginDate, DateTimeFormatter.ofPattern(“yyyy-MM-dd”));
LocalDate转String
LocalDate date = LocalDate.now(); //获取当前时间
DateTimeFormatter dtf= DateTimeFormatter.ofPattern("yyyy-MM-dd"); //定义时间格式
String dateStr = date.format(dtf); //转换成String类型
查询某天的数据:
可以获取指定时间的后一天时间=大于等于当天,小于后一天
specifiedDay是指定的日期【2022-06-11】
/**
* 获得指定日期的后一天
*
* @param specifiedDay yy-MM-dd
* @return
*/
public static String getAfterDay(String specifiedDay)
{
Calendar c = Calendar.getInstance();
Date date = null;
try
{
date = new SimpleDateFormat("yy-MM-dd").parse(specifiedDay);
}
catch (ParseException e)
{
e.printStackTrace();
}
c.setTime(date);
int day = c.get(Calendar.DATE);
c.set(Calendar.DATE, day + 1);
String dayAfter = new SimpleDateFormat("yy-MM-dd").format(c.getTime());
return dayAfter;
}
if (Objects.nonNull(req.getStartTime())) {
lambdaQueryWrapper.ge(User::getLoginTime, req.getStartTime()).lt(User::getLoginTime,getAfterDay(req.getStartTime()));
}
时间模糊查询
sql语句【时间实体类类型给的是String类型】
SELECT count(*)
FROM user
WHERE login_time LIKE '%2022-05%'
mybatisePlus模糊查询
if (Objects.nonNull(req.getStartTime())) {
lambdaQueryWrapper.like(User::getLoginTime,req.getStartTime());
}