JQuery根据时间查询Pgsql中的数据

需求：使用JQuery,操作pg数据库，查询一天之内的打卡记录
前提：pgsql数据库;数据库中打卡时间类型为timestamp;
前端的传过来的时间类型为String
做法：
第一步：添加注解

@DateTimeFormat(pattern = "yyyy-MM-dd") Date clockTime

第二步：

booleanBuilder.and(attendanceEntity.clockTime.between(DateUtil.beginOfDay(clockTime),DateUtil.endOfDay(clockTime)));

完整代码例子

//工具生成的Querydsl实体类
 QAttendanceEntity attendanceEntity = QAttendanceEntity.attendanceEntity;
        //StringTemplate dateExpr = Expressions.stringTemplate("to_date({0}::text,'YYYY-MM-DD')",attendanceEntity.clockTime);

        BooleanBuilder booleanBuilder = new BooleanBuilder(
                attendanceEntity.status.eq(0)
        );

        if (clockTime != null){
                 booleanBuilder.and(attendanceEntity.clockTime.between(DateUtil.beginOfDay(clockTime),DateUtil.endOfDay(clockTime)));
        }
        if (StringUtils.isNotEmpty(username)){
            booleanBuilder.and(attendanceEntity.userName.eq(username));
        }

        List<AttendanceVO> collect = queryFactory.selectFrom(attendanceEntity)
                .where(booleanBuilder)
                .orderBy(attendanceEntity.createTime.desc())
                .fetch()
                .stream()
                .map(entity -> {
                    AttendanceVO attendanceVO = new AttendanceVO();
                    BeanUtil.copyProperties(entity, attendanceVO);
                    return attendanceVO;
                }).collect(Collectors.toList());
        return ResultUtil.success(collect);

其他解决办法：
也可以直接使用

@Autowired
 private JdbcTemplate jt;

封装原生sql,例子为

 StringBuilder sb = new StringBuilder();
        sb.append("select \n" +
                "t_a_i.user_id as userId,\n" +

                "t_a_m.id as id,\n" +
                "t_a_m.attendance_id as attendanceId,\n" +
                "t_a_m.forget_times as forgetTimes,\n" +
                "t_a_m.late_times as lateTimes,\n" +
                "t_a_m.trip_days as tripDays,\n" +
                "t_a_m.sick_leave as sickLeave,\n" +
                "t_a_m.affair_leave as affairLeave,\n" +
                "t_a_m.work_overtime as workOverTime,\n" +
                "t_a_m.settled_work_overtime as settledWorkOverTime,\n" +
                "t_a_m.holiday_work_overtime as holidayWorkOvertime\n" +
                "from t_attendance_months as t_a_m\n" +
                "left join t_attendance_info as t_a_i\n" +
                "on t_a_i.id = t_a_m.attendance_id\n" +
                "where t_a_i.employee_code = '");
        sb.append(employeeCode).append("'and t_a_i.is_delete = 0\n");
        if (!StringUtils.isEmpty(time)) {
            sb.append("and to_date(t_a_m.create_time, 'yyyy-mm-dd HH:ii:ss') like '");
            sb.append(time).append("%'");
        }
        sb.append("ORDER BY t_a_i.months DESC");
        try {
            return jt.query(sb.toString(),new BeanPropertyRowMapper<>(AttendanceMonthVO.class));
        } catch (Exception e) {
            return null;
        }

方式很多，第一种比较优雅，以后代码维护更方便